MSSQL Pivot Table

hooi1983

有一个 table，有 12 种 types，每种 type 有 7 种 positions，每种 position 有两个 groups，每条 line 都有 date & amount
现在我要拿出每个 type，每个 position of type，每个 group of position of type 的 sum of amount based on date

现在我暂时用 select statement 来将它们变成一个 table，有 12 + 12*7 + 12*7*2 个 select statements，好像很苯

如果用 pivot table，type 只需要一个 table，position of type 需要 12 个 tables，group of position of type 需要 12*7 个 tables，也是麻烦

各位大大有没有什么更好的方法？
谢谢

sfkwan

该table含有Type, Position, Group, date, amount Columns?
或许你可以给dummy data,会比较容易明白你的问题

hooi1983

回复 2# sfkwan

sample data

01-May, type 1, position 1, group 1, 10.00
01-May, type 1, position 1, group 2, 12.00
01-May, type 1, position 2, group 2, 40.00
01-May, type 2, position 1, group 1, 13.00
01-May, type 2, position 1, group 2, 14.00


===============================


sample output


Date | type 1 | type 2 | Total
01-May | 62.00 | 27.00 | 89.00


Type 1


01-May | position 1 | position 2 | Total
group 1 | 10.00 | 0.00 | 10.00
group 2 | 12.00 | 40.00 | 52.00
Total | 22.00 | 40.00 | 62.00


Type 2


01-May | position 1 | position 2 | Total
group 1 | 13.00 | 0.00 | 13.00
group 2 | 14.00 | 0.00 | 14.00
Total | 27.00 | 0.00 | 27.00

sfkwan

回复 3# hooi1983


    我所会的方法就只有利用 Cursor，希望能帮到你

Dummy data

1. insert into sample values ('1/may/2010', 'type 1', 'pos 1', 'group 1', 10)
   
2. insert into sample values ('1/may/2010', 'type 1', 'pos 1', 'group 2', 12)
   
3. insert into sample values ('1/may/2010', 'type 1', 'pos 2', 'group 2', 40)
   
4. insert into sample values ('1/may/2010', 'type 2', 'pos 1', 'group 1', 13)
   
5. insert into sample values ('1/may/2010', 'type 2', 'pos 1', 'group 2', 14)
   

复制代码

1. 
   
2. SELECT date as 'Date',
   
3.                 'Type 1' = SUM(CASE WHEN [type] = 'type 1' THEN amount END),
   
4.                 'Type 2' = SUM(CASE WHEN [type] = 'type 2' THEN amount END),
   
5.                 'Total' = SUM(amount)
   
6. FROM sample
   
7. Group by date
   

复制代码

1. 
   
2. DECLARE @targetType varchar(50)
   
3. DECLARE typeCursor CURSOR FOR
   
4. SELECT distinct [type] from sample
   
5. OPEN typeCursor
   
6. 
   
7. FETCH NEXT FROM typeCursor INTO @targetType
   
8. 
   
9. WHILE @@FETCH_STATUS=0
   
10. BEGIN
    
11. 
    
12. SELECT [group] ,
    
13.                 'Position 1' = SUM(CASE WHEN [position] = 'pos 1' THEN amount END),
    
14.                 'Position 2' = SUM(CASE WHEN [position] = 'pos 2' THEN amount END),
    
15.                 'Total' = SUM(amount)
    
16. FROM sample
    
17. WHERE [type] = @targetType
    
18. Group by [group]
    
19. UNION
    
20. SELECT 'Total' as 'group',
    
21.                 'Position 1' = SUM(CASE WHEN [position] = 'pos 1' THEN amount END),
    
22.                 'Position 2' = SUM(CASE WHEN [position] = 'pos 2' THEN amount END),
    
23.                 'Total' = SUM(amount)
    
24. FROM sample
    
25. WHERE [type] = @targetType
    
26. 
    
27. FETCH NEXT FROM typeCursor INTO @targetType
    
28. END
    
29. CLOSE typeCursor
    
30. DEALLOCATE typeCursor
    

复制代码

hooi1983

回复 4# sfkwan

我就是不要用这种方法我是希望用 Pivot 可以直接拿到

sfkwan

回复 5# hooi1983


    那你想要怎么样的Output? 不好意思，不是很明白你的要求。

hooi1983

回复 6# sfkwan

之前我用 select 来 query看了你的 sample 后，发现原来用 case 就可以了
还是要谢谢你


由于 Type, Position and Group 不是 dynamic 的，
我现在用


select date,'TotalAmount' = sum(amount),
'Type1Total' = sum(case when type='Type1' then amount end),
'Type2Total' = sum(case when type='Type2' then amount end),
'Type1Position1Total' = sum(case when type='Type1' and position='Position1' then amount end),
'Type1Position2Total' = sum(case when type='Type1' and position='Position1' then amount end),
'Type1Position1Group1Total' = sum(case when type='Type1' and position='Position1' and group='Group1' then amount end),
'Type1Position1Group2Total' = sum(case when type='Type1' and position='Position1' and group='Group2' then amount end)
where date='01-May-2010'
group by date


拿到这些 values 后再填充

sfkwan

回复 7# hooi1983


    不用客气

如果要做到Dynamic，用Stored Procedure/Function 比较Flexible，而且容易

hooi1983

回复 8# sfkwan

目前这些 field types 是 fixed 了的，所以用 select + case 就可以了还不需要用到 stored procedure