create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)select * from test
id name quarter profile
----------- -------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500(8 row(s) affected)使用PIVOT将四个季度的利润转换成横向显示:select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvtid name 一季度 二季度 三季度 四季度
-------- --------- ----------- -------- ------- -------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500(2 row(s) affected)上面的例子,select id,name,[1] as "一季度",[2] as "二季度",[3] as "三季度",[4] as "四季度" from test pivot(sum(profile) for quarter in([1],[2],[3],[4]))as pvt
运行时报错,
消息 102,级别 15,状态 1,第 1 行
'(' 附近有语法错误。
网上搜了好几个pivot的例子,都报同样的错误,
使用的是sqlserver 2008
请问是哪里出错了?谢谢
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)select * from test
id name quarter profile
----------- -------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500(8 row(s) affected)使用PIVOT将四个季度的利润转换成横向显示:select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvtid name 一季度 二季度 三季度 四季度
-------- --------- ----------- -------- ------- -------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500(2 row(s) affected)上面的例子,select id,name,[1] as "一季度",[2] as "二季度",[3] as "三季度",[4] as "四季度" from test pivot(sum(profile) for quarter in([1],[2],[3],[4]))as pvt
运行时报错,
消息 102,级别 15,状态 1,第 1 行
'(' 附近有语法错误。
网上搜了好几个pivot的例子,都报同样的错误,
使用的是sqlserver 2008
请问是哪里出错了?谢谢
解决方案 »
- 在网上找了个把word文件存在数据库中的存储过程,但在自己的机器上试验读入word文件时候并不成功,提示“'textcopy' 不是内部或外部命令,也不是可运行的程序或批处理文件。NULL”这个过程有什么问题吗?存储过程如下(根据实际情况自己做了修改):
- 请教sql语句,关于分组?急!!!
- 这个简单的触发程序怎么写?
- SQL SERVER 数据库连接问题 在线等待
- 很奇怪的一个mssql查询语句
- 从表随机取出10条记录,但是要求数据集是按照自动编号进行排列,如果做?
- ******存储过程动态为SQL传参数问题
- 启动安装程序然后无反映,安装不了sql server 2000
- DTS是什么?有什么用?怎样用?
- 谁能讲讲sql server7 和 sql server 2000 到底有什么区别?
- SQL 一列数据转化为一行
- ORDER BY NEWID()的问题
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)select id ,name,
[1] as [一季度],
[2] as [二季度],
[3] as [三季度],
[4] as [四季度]
from
(
select * from test a pivot (max(profile) for quarter in ([1],[2],[3],[4])) b
) mdrop table test/*
id name 一季度 二季度 三季度 四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500(2 行受影响)
*/
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test AS T
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from test
pivot (sum(profile) for quarter in ([1],[2],[3],[4]) )
as pvt--result:
id name 一季度 二季度 三季度 四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500(2 行受影响)
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)
select id ,name,
[1] as [一季度],
[2] as [二季度],
[3] as [三季度],
[4] as [四季度] from test a pivot (sum(profile) for quarter in ([1],[2],[3],[4])) bdrop table test/*
id name 一季度 二季度 三季度 四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500(2 行受影响)*/
alter database Northwind SET COMPATIBILITY_LEVEL=100
至少得为90才可以的