StoreCode GoodsCode Size Color Num
s1 g1 165 01 2
s1 g2 170 01 1
s1 g1 170 02 3
s2 g1 165 01 4
s2 g2 170 01 5
......
显示结果如下:
GoodsCode Size Color s1 s2 ColSum
g1 165 01 2 4 6
g1 170 02 3 0 3
g2 170 01 1 5 6
6 9 15
也有可能这样显示
GoodsCode s1 s2 ColSum
g1 5 4 9
g2 1 5 6
null 6 9 15
解答
Insert TEST
Select ' s1 ' , ' g1 ' , ' 165 ' , ' 01 ' , ' 2 '
Union All
Select ' s1 ' , ' g2 ' , ' 170 ' , ' 01 ' , ' 1 '
Union All
Select ' s1 ' , ' g1 ' , ' 170 ' , ' 02 ' , ' 3 '
Union All
Select ' s2 ' , ' g1 ' , ' 165 ' , ' 01 ' , ' 4 '
Union All
Select ' s2 ' , ' g2 ' , ' 170 ' , ' 01 ' , ' 5 '
Union All
Select ' s3 ' , ' g2 ' , ' 170 ' , ' 01 ' , ' 5 '
Union All
Select ' s4 ' , ' g3 ' , ' 170 ' , ' 01 ' , ' 5 '
-- 测试
Declare @S Varchar ( 1000 )
Set @S = ' Select GoodsCode '
Select @S = @S + ' , ' + ' SUM(Case StoreCode When ''' + StoreCode + ''' Then Num Else 0 End) As ' + StoreCode
from ( Select Distinct StoreCode from TEST ) A Order by StoreCode
Set @S = @S + ' ,SUM(Num) As ColSum from TEST Group By GoodsCode With Rollup '
print @s
EXEC ( @S )
-- 删除测试环境
Drop Table TEST
-- 结果
/**/ /*
GoodsCode s1 s2 ColSum
g1 5 4 9
g2 1 5 6
NULL 6 9 15
*/