590
社区成员
发帖
与我相关
我的任务
分享
/****
SELECT 非透视列,
[透视列 1] AS '列名1',
[透视列 2] AS '列名2',
[透视列 3] AS '列名3'
FROM (
-- 源数据
SELECT 非透视列,
透视列值的来源列,
需要聚合的值
FROM 表
)AS 别名
PIVOT
(
SUM(需要聚合的值)
FOR 透视列值的来源列 IN ([透视列 1],[透视列 2],[透视列 3])
)AS 别名
****/
DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT ROW_NUMBER()over(order by unitname) as id,y,Name1,Name2,Name3,UnitName,'
SELECT @sql = @sql + 'SUM(CASE M WHEN ' + M +' THEN iNum ELSE 0 END) AS '''+ M +'月数量''
,SUM(CASE M WHEN ' + M + 'THEN RMB ELSE 0 END) AS '''+ M +'RMB''
,SUM(CASE M WHEN ' + M + 'THEN US ELSE 0 END) AS '''+ M +'US''
,SUM(CASE M WHEN ' + M + 'THEN JPY ELSE 0 END) AS '''+ M +'JPY''
,SUM(CASE M WHEN ' + M + 'THEN ALLSum ELSE 0 END) AS '''+ M +'金额'','
FROM (SELECT DISTINCT m FROM #PO) AS a
select @sql
select @sql = LEFT(@sql,LEN(@sql)-1) + ' from #po group by y,Name1,Name2,name3,UnitName'
exec(@sql)
找到解决方法了
declare @m varchar(255) select @m=ISNULL(@m+',','') + '['+ CAST(m as varchar(255)) + ']' from #PO group by m
declare @sql varchar(max)
set @sql ='select * from (select id,y,m,Name1,Name2,Name3,UnitName,RMB from #PO)a PIVOT
(sum(RMB) for M in ('+ @m +'))as pvt '
select @sql
exec(@sql)
因为这个是列名动态的,所以不能用静态的方法。。SELECT 非透视列,
SUM(CASE 透视列值的来源列 WHEN '透视列 1' THEN 需要聚合的值1 END) AS '列名11',
SUM(CASE 透视列值的来源列 WHEN '透视列 1' THEN 需要聚合的值2 END) AS '列名12',
SUM(CASE 透视列值的来源列 WHEN '透视列 2' THEN 需要聚合的值1 END) AS '列名21',
SUM(CASE 透视列值的来源列 WHEN '透视列 2' THEN 需要聚合的值2 END) AS '列名22',
SUM(CASE 透视列值的来源列 WHEN '透视列 3' THEN 需要聚合的值1 END) AS '列名31',
SUM(CASE 透视列值的来源列 WHEN '透视列 3' THEN 需要聚合的值2 END) AS '列名32'
FROM (
-- 源数据
SELECT 非透视列,
透视列值的来源列,
需要聚合的值1, 需要聚合的值2
FROM 表
)AS 别名