列转行问题

[iamggggs]

测试数据：

;with A as
(
 select '1' as 账号
       ,'A' as 产品代码
	   ,100 as 产品市值
 union all
 select '1' as 账号
       ,'B' as 产品代码
	   ,50 as 产品市值
 union all
 select '2' as 账号
       ,'A' as 产品代码
	   ,200 as 产品市值
 union all
 select '3' as 账号
       ,'A' as 产品代码
	   ,100 as 产品市值
 union all
 select '3' as 账号
       ,'B' as 产品代码
	   ,80 as 产品市值
 union all
 select '4' as 账号
       ,'C' as 产品代码
	   ,10 as 产品市值
)

select *
from A

引用

账号 产品代码 产品市值
1 A 100
1 B 50
2 A 200
3 A 100
3 B 80
4 C 10

需要得到的结果：按账号汇总不同产品的产品市值，没有的填0（实际数据上万条）：

引用

账号 A B C
1 100 50 0
2 200 0 0
3 100 80 0
4 0 0 10

[iamggggs]

补充说明：实际数据记录上万条，产品代码可能上千个。

[RINK_1]

产品代码只有ABC3种？

[iamggggs]

引用 3 楼 RINK_1 的回复:

产品代码只有ABC3种？

二楼我有补充说明：实际数据记录上万条，产品代码可能上千个。

[iamggggs]

自己网上搜索了一下，可以用PIVOT，比如以上例子可以这样查：

SELECT * FROM A /*数据源*/
AS P
PIVOT 
(
    SUM(产品市值 /*行转列后 列的值*/) FOR 
	p.产品代码 /*需要行转列的列*/ IN ([A],[B],[C])/*列的值*/
) AS T

得到结果：

账号	A	B	C
1	100	50	NULL
2	200	NULL	NULL
3	100	80	NULL
4	NULL	NULL	10

但我不知道怎么把语句中的

[A],[B],[C]

用动态语句表达出来，其实产品代码范围就是：

select distinct(产品代码) from A

[RINK_1]

CREATE TABLE #T
(ACCOUNT_ID VARCHAR(10),
 ITEM_CODE VARCHAR(10),
 VALUE INT)

INSERT INTO #T
select '1' as 账号
       ,'A' as 产品代码
       ,100 as 产品市值
 union all
 select '1' as 账号
       ,'B' as 产品代码
       ,50 as 产品市值
 union all
 select '2' as 账号
       ,'A' as 产品代码
       ,200 as 产品市值
 union all
 select '3' as 账号
       ,'A' as 产品代码
       ,100 as 产品市值
 union all
 select '3' as 账号
       ,'B' as 产品代码
       ,80 as 产品市值
 union all
 select '4' as 账号
       ,'C' as 产品代码
       ,10 as 产品市值

DECLARE @SQL VARCHAR(8000)

SELECT @SQL=ISNULL(@SQL+',','')+'['+ITEM_CODE+']'
FROM (SELECT DISTINCT ITEM_CODE FROM #T) AS A


SET @SQL='SELECT *
FROM #T
PIVOT (SUM(VALUE) FOR ITEM_CODE IN ('+@SQL+')) AS B'


EXEC(@SQL)

[iamggggs]

引用 6 楼 RINK_1 的回复:

CREATE TABLE #T
(ACCOUNT_ID VARCHAR(10),
 ITEM_CODE VARCHAR(10),
 VALUE INT)

INSERT INTO #T
select '1' as 账号
       ,'A' as 产品代码
       ,100 as 产品市值
 union all
 select '1' as 账号
       ,'B' as 产品代码
       ,50 as 产品市值
 union all
 select '2' as 账号
       ,'A' as 产品代码
       ,200 as 产品市值
 union all
 select '3' as 账号
       ,'A' as 产品代码
       ,100 as 产品市值
 union all
 select '3' as 账号
       ,'B' as 产品代码
       ,80 as 产品市值
 union all
 select '4' as 账号
       ,'C' as 产品代码
       ,10 as 产品市值

DECLARE @SQL VARCHAR(8000)

SELECT @SQL=ISNULL(@SQL+',','')+'['+ITEM_CODE+']'
FROM (SELECT DISTINCT ITEM_CODE FROM #T) AS A


SET @SQL='SELECT *
FROM #T
PIVOT (SUM(VALUE) FOR ITEM_CODE IN ('+@SQL+')) AS B'


EXEC(@SQL)

感谢，学了个字串符拼接：

SELECT @SQL=ISNULL(@SQL+',','')+'['+ITEM_CODE+']'
FROM (SELECT DISTINCT ITEM_CODE FROM #T) AS A