求借助另一个表进行 行转列的sql语句
一个小超市把很多商品分成了几个大类 ，例如生鲜类 粮油类 日杂类 糕点类 文具类
有一个表a
类别id 类别名称
1 生鲜类
2 粮油类
3 日杂类
4 糕点类
5 文具类
.......
每天要卖很多商品，商品有具体的名字 ，但都属于某一类
有个销售记录表
表b
纪录id 销售日期 类别名称 商品名称 价格
1 20130701 文具类 铅笔 10.00
2 20130701 文具类 橡皮 25.00
3 20130701 生鲜类 猪肉 5.50
4 20130702 文具类 尺子 3.20
。。。。。。
用户需要取某个时间段内每天的各种大类别的销售量，时间段由用户选择
如果从销售记录表进行行转列
是能够统计出结果的
但结果是如下这样
日期生鲜类文具类合计
20130701 1 2 3
20130702 0 1 1
也就是如果时间段内某一类商品一个也没销售出去，那统计出来的结果就没有这个类别
我想用到类别表，让所有的类别都显示出来，如果没销售就是0 这个sql该如何写呢
也就是需要得到如下结果
日期生鲜类粮油类日杂类糕点类文具类合计
20130701 1 0 0 0 2 3
20130702 0 0 0 0 1 1
[解决办法]
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 类别名称 from (SELECT T.类别名称 ,
T.销售日期 ,
SUM(CASE WHEN 价格 IS NULL THEN 0
ELSE 1
END) AS num
FROM ( SELECT TBA.类别名称 ,
TBB.销售日期
FROM dbo.TBB
CROSS JOIN TBA
GROUP BY TBA.类别名称 ,
TBB.销售日期
) T
LEFT JOIN dbo.TBB ON T.类别名称 = TBB.类别名称
AND T.销售日期 = dbo.TBB.销售日期
GROUP BY T.类别名称 ,
T.销售日期)T1 group by 类别名称
set @sql = '[' + @sql + ']'
exec ('select * from (select * from (SELECT T.类别名称 ,
T.销售日期 ,
SUM(CASE WHEN 价格 IS NULL THEN 0
ELSE 1
END) AS num
FROM ( SELECT TBA.类别名称 ,
TBB.销售日期
FROM dbo.TBB
CROSS JOIN TBA
GROUP BY TBA.类别名称 ,
TBB.销售日期
) T
LEFT JOIN dbo.TBB ON T.类别名称 = TBB.类别名称
AND T.销售日期 = dbo.TBB.销售日期
GROUP BY T.类别名称 ,
T.销售日期)T1) a pivot (max(num) for 类别名称 in (' + @sql + ')) b')
/*
销售日期糕点类粮油类日杂类生鲜类文具类
2013-07-01 00:00:00.00000012
2013-07-02 00:00:00.00000001*/
create table a(类别id int,类别名称 varchar(10))
insert into a values(1 , '生鲜类')
insert into a values(2 , '粮油类')
insert into a values(3 , '日杂类')
insert into a values(4 , '糕点类')
insert into a values(5 , '文具类')
go
create table b(纪录id int, 销售日期 datetime, 类别名称 varchar(10), 商品名称 varchar(10),价格 decimal(18,2))
insert into b values(1 , '20130701', '文具类', '铅笔' , 10.00)
insert into b values(2 , '20130701', '文具类', '橡皮' , 25.00)
insert into b values(3 , '20130701', '生鲜类', '猪肉' , 5.50)
insert into b values(4 , '20130702', '文具类', '尺子' , 3.20)
go
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2013-07-01'
set @dt2 = '2013-07-02'
declare @sql varchar(8000)
set @sql = 'select convert(varchar(8),m.销售日期,112) 日期 '
select @sql = @sql + ' , sum(case 类别名称 when ''' + n.类别名称 + ''' then 1 else 0 end) [' + 类别名称 + ']'
from (select 类别名称 from a ) as n
set @sql = @sql + ' , count(1) 合计 from b m where convert(varchar(8),m.销售日期,112) between ''' + convert(varchar(8),@dt1,112) + ''' and ''' + convert(varchar(8),@dt2,112) + ''' group by convert(varchar(8),m.销售日期,112)'
exec(@sql)
/*
日期 生鲜类 粮油类 日杂类 糕点类 文具类 合计
-------- ----------- ----------- ----------- ----------- ----------- -----------
20130701 1 0 0 0 2 3
20130702 0 0 0 0 1 1
*/
drop table a , b
