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