当前位置: 代码迷 >> Sql Server >> 使用group by後,要怎么同時sum與pivot呢
  详细解决方案

使用group by後,要怎么同時sum與pivot呢

热度:25   发布时间:2016-04-24 09:01:49.0
使用group by後,要如何同時sum與pivot呢?
本帖最后由 ben12100515 于 2015-06-08 08:08:10 编辑
item(int) type(char) qty(int) name(char)
  1    A    10   K2
  1    A    15   K5
  1    B    10   K1
  1    D    20   P4
  3    B    5    P1
  3    B    15   K6
  3    B    10   K3
  3    E    25   P4
  3    E    20   K5
  4    C    15   K1
  4    D    10   P6
  4    D    15   P2
  4    D    5    P3
  6    A    5   K2
  6    A    15   K3
  6    A    20   P6
  6    A    15   P1
  6    C    5   K4

原table如上,要如何select成下面的模样呢?
1. 同样的item整理成一列
2. 把item里的type排在后面(type只有A,B,C,D,E 5种)
3. 相同item&相同type的qty-->作总和
4. 相同item&相同type的name-->以横线串接起来

item A-qty A-name B-qty B-name C-qty C-name D-qty D-name E-qty E-name
 1  25  K2-K5  10   K1          20   P4
 3          30 P1-K6-K3                 45  P4-K5
 4                  15   K1  30  P6-P2-P3
 6  55 K2-K3-P6-P1        5   K4

------解决思路----------------------
--动态
DECLARE @SQL VARCHAR(MAX)
SET @SQL='SELECT item'
SELECT @SQL=@SQL+',SUM(CASE WHEN[type]='''+[type]+'''THEN qty END)['+[type]+'-qty]'
+',STUFF((SELECT ''-''+[name]
FROM TB T2
WHERE T1.item=T2.item AND T2.[type]='''+[type]+'''
FOR XML PATH(''''))
,1,1,'''')['+[type]+'-name]'
FROM TB GROUP BY [type]
SET @SQL=@SQL+'FROM TB T1 GROUP BY item ORDER BY item'
PRINT @SQL
EXEC(@SQL)
--静态
SELECT item
,SUM(CASE WHEN[type]='A'THEN qty END)[A-qty]
,STUFF((SELECT '-'+[name]
FROM TB T2
WHERE T1.item=T2.item AND T2.[type]='A'
FOR XML PATH(''))
,1,1,'')[A-name]

,SUM(CASE WHEN[type]='B'THEN qty END)[B-qty]
,STUFF((SELECT '-'+[name]
FROM TB T2
WHERE T1.item=T2.item AND T2.[type]='B'
FOR XML PATH(''))
,1,1,'')[B-name]

,SUM(CASE WHEN[type]='C'THEN qty END)[C-qty]
,STUFF((SELECT '-'+[name]
FROM TB T2
WHERE T1.item=T2.item AND T2.[type]='C'
FOR XML PATH(''))
,1,1,'')[C-name]

,SUM(CASE WHEN[type]='D'THEN qty END)[D-qty]
,STUFF((SELECT '-'+[name]
FROM TB T2
WHERE T1.item=T2.item AND T2.[type]='D'
FOR XML PATH(''))
,1,1,'')[D-name]

,SUM(CASE WHEN[type]='E'THEN qty END)[E-qty]
,STUFF((SELECT '-'+[name]
FROM TB T2
WHERE T1.item=T2.item AND T2.[type]='E'
FOR XML PATH(''))
,1,1,'')[E-name]
FROM TB T1
GROUP BY item
ORDER BY item
  相关解决方案