select YEAR NIAN,'1' as 1月,'2 'as 2月,'3' as 3月,'4' as 月 from tab pivot (sum(amount) for month in( '1','2','3','4'))as p group by year消息 102,级别 15,状态 1,第 3 行 '1' 附近有语法错误。
select YEAR NIAN,'1' as 1月,'2 'as 2月,'3' as 3月,'4' as 月 改成 select YEAR NIAN,'1' as [1月],'2 'as [2月],'3' as [3月],'4' as [4月]
[]--数字要加转义符 e.g.SELECT YEAR NIAN , [1] AS [1月] , [2] AS [2月] , [3] AS [3月] , [4] AS [4月] FROM tab PIVOT ( SUM(amount) FOR month IN ( [1], [2], [3], [4] ) )AS p GROUP BY year;
消息 8120,级别 16,状态 1,第 3 行 选择列表中的列 'p.1' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。 执行下面语句报错 select YEAR NIAN, [1] [一月], [2] [二月], [3] [三月], [4] [四月] from tab pivot (sum(amount) for month in( [1],[2],[3],[4]))as p group by year 下面是表 year month amount 1991 1 100 1991 2 200 1991 3 100 1991 4 100 1992 1 100 1992 2 1000 1992 3 200 1992 4 300
少去掉group by ,PIVOT把没有行转列的都会以分组的方式显示 e.g. use Tempdb go --> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([year] Date,[month] int,[amount] int) Insert #T select '1991',1,100 union all select '1991',2,200 union all select '1991',3,100 union all select '1991',4,100 union all select '1992',1,100 union all select '1992',2,1000 union all select '1992',3,200 union all select '1992',4,300 GoSELECT YEAR NIAN , [1] AS [1月] , [2] AS [2月] , [3] AS [3月] , [4] AS [4月] FROM #T PIVOT ( SUM(amount) FOR month IN ( [1], [2], [3], [4] ) )AS p/* NIAN 1月 2月 3月 4月 1991-01-01 100 200 100 100 1992-01-01 100 1000 200 300 */
改成
select YEAR NIAN,'1' as [1月],'2 'as [2月],'3' as [3月],'4' as [4月]
e.g.SELECT YEAR NIAN ,
[1] AS [1月] ,
[2] AS [2月] ,
[3] AS [3月] ,
[4] AS [4月]
FROM tab PIVOT ( SUM(amount) FOR month IN ( [1], [2], [3], [4] ) )AS p
GROUP BY year;
选择列表中的列 'p.1' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
执行下面语句报错
select YEAR NIAN,
[1] [一月],
[2] [二月],
[3] [三月],
[4] [四月]
from tab
pivot (sum(amount) for month in( [1],[2],[3],[4]))as p
group by year
下面是表
year month amount
1991 1 100
1991 2 200
1991 3 100
1991 4 100
1992 1 100
1992 2 1000
1992 3 200
1992 4 300
e.g.
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([year] Date,[month] int,[amount] int)
Insert #T
select '1991',1,100 union all
select '1991',2,200 union all
select '1991',3,100 union all
select '1991',4,100 union all
select '1992',1,100 union all
select '1992',2,1000 union all
select '1992',3,200 union all
select '1992',4,300
GoSELECT YEAR NIAN ,
[1] AS [1月] ,
[2] AS [2月] ,
[3] AS [3月] ,
[4] AS [4月]
FROM #T PIVOT ( SUM(amount) FOR month IN ( [1], [2], [3], [4] ) )AS p/*
NIAN 1月 2月 3月 4月
1991-01-01 100 200 100 100
1992-01-01 100 1000 200 300
*/