分类汇总问题！
表1
product type count
aa 1 10
aa 2 19
bb 1 23
bb 2 60
cc 1 6
现在要得到如下
product type1 type1_count type2 type2_count
aa 1 10 2 19
bb 1 23 2 60
cc 1 6 2 0
请教了！
[解决办法]
--結果沒貼好
Create Table 表1
(productVarchar(10),
typeInt,
[count]Int)
Insert 表1 Select 'aa ', 1, 10
Union All Select 'aa ', 2, 19
Union All Select 'bb ', 1, 23
Union All Select 'bb ', 2, 60
Union All Select 'cc ', 1, 6
GO
--如果type是固定的
Select
product,
1 As type1,
SUM(Case type When 1 Then [count] Else 0 End) As type1_count,
2 As type2,
SUM(Case type When 2 Then [count] Else 0 End) As type2_count
From
表1
Group By product
--如果type不是固定的
Declare @S Varchar(8000)
Select @S = 'Select product '
Select @S = @S + ' , ' + Rtrim(type) + ' As type1, SUM(Case type When ' + Rtrim(type) + ' Then [count] Else 0 End) As type ' + Rtrim(type) + '_count '
From 表1 Group By type
Select @S = @S + ' From表1 Group By product '
EXEC(@S)
GO
Drop Table 表1
--Result
/*
producttype1type1_counttype2type2_count
aa110219
bb123260
cc1620
*/
