create table shuju
(
pm varchar(20), --品名
rq datetime, --日期
sl int --数量
)insert into shuju values('a','2010-1-1',10)
insert into shuju values('b','2010-1-1',15)
insert into shuju values('b','2010-1-2',20)
insert into shuju values('c','2010-1-2',30)
insert into shuju values('a','2010-1-3',10)
insert into shuju values('b','2010-1-3',12)
insert into shuju values('a','2010-1-3',10)select * from shuju--------------------------------------------------------------------------------------
select sum(case when convert(nvarchar(10),rq,120)=CONVERT(NVARCHAR(10),'2010-01-01 00:00:00.000',120)
then
sl
else
0
end)
as ''+convert(varchar(10),rq,120)+''报错提示为:
消息 1038,级别 15,状态 3,第 7 行
不能使用空白的对象或列名。如果必要,请使用一个空格。
(
pm varchar(20), --品名
rq datetime, --日期
sl int --数量
)insert into shuju values('a','2010-1-1',10)
insert into shuju values('b','2010-1-1',15)
insert into shuju values('b','2010-1-2',20)
insert into shuju values('c','2010-1-2',30)
insert into shuju values('a','2010-1-3',10)
insert into shuju values('b','2010-1-3',12)
insert into shuju values('a','2010-1-3',10)select * from shuju--------------------------------------------------------------------------------------
select sum(case when convert(nvarchar(10),rq,120)=CONVERT(NVARCHAR(10),'2010-01-01 00:00:00.000',120)
then
sl
else
0
end)
as ''+convert(varchar(10),rq,120)+''报错提示为:
消息 1038,级别 15,状态 3,第 7 行
不能使用空白的对象或列名。如果必要,请使用一个空格。
then
sl
else
0
end)
as [2010-01-01]
from shuju
then
sl
else
0
end)
as '+convert(varchar(10),rq,120)+'
then
sl
else
0
end)
as '2010-01-01'
(
pm varchar(20), --品名
rq datetime, --日期
sl int --数量
)
insert into shuju values('a','2010-1-1',10)
insert into shuju values('b','2010-1-1',15)
insert into shuju values('b','2010-1-2',20)
insert into shuju values('c','2010-1-2',30)
insert into shuju values('a','2010-1-3',10)
insert into shuju values('b','2010-1-3',12)
insert into shuju values('a','2010-1-3',10)select * from shuju
--------------------------------------------------------------------------------------
select sum(case when convert(nvarchar(10),rq,120)=CONVERT(NVARCHAR(10),'2010-01-01 00:00:00.000',120)
then
sl
else
0
end)
as ''+convert(varchar(10),rq,120)+''--不能这样使用报错提示为:
消息 1038,级别 15,状态 3,第 7 行
不能使用空白的对象或列名。如果必要,请使用一个空格。
then
sl
else
0
end)
as ''+convert(varchar(10),rq,120)+'' FROM TB?
如:
http://topic.csdn.net/u/20100201/09/71cf73a9-bd98-4774-86b0-25e25325ff17.html?58856
'''+CONVERT(NVARCHAR(10),[日期],120)+'''
declare @sql varchar(8000)select @sql = isnull(@sql+',
','')+'sum(case convert(varchar(10),rq,120) when '''+rq+''' then sl else 0 end) as ['+rq+']'
from (select distinct convert(varchar(10),rq,120) as rq from shuju) tselect @sql = 'select pm,'+@sql + ' from shuju group by pm,convert(varchar(10),rq,120)'exec(@sql)
我是看了鸭哥代码来写的啊。。
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
[品名] VARCHAR(10)
,[日期] DATETIME
,[数量] INT
)
INSERT INTO TB
SELECT 'a','2010-1-1', 10 UNION ALL
SELECT 'b','2010-1-1', 15 UNION ALL
SELECT 'b','2010-1-2', 20 UNION ALL
SELECT 'c','2010-1-2', 30 UNION ALL
SELECT 'a','2010-1-3', 10 UNION ALL
SELECT 'b','2010-1-3', 12 UNION ALL
SELECT 'a','2010-1-3', 10 DECLARE @STR NVARCHAR(4000)
SELECT @STR='SELECT [品名]
'
SELECT @STR=@STR+',SUM(CASE WHEN [日期]='''+CONVERT(NVARCHAR(10),[日期],120)+''' THEN [数量] ELSE 0 END) AS '''+CONVERT(NVARCHAR(10),[日期],120)+'''
'
FROM (SELECT DISTINCT [日期] FROM TB ) T ORDER BY [日期]
SELECT @STR=@STR+'FROM TB
GROUP BY [品名]'EXEC (@STR)
/*
品名 2010-01-01 2010-01-02 2010-01-03
---------- ----------- ----------- -----------
a 10 0 20
b 15 20 12
c 0 30 0
*/
','')+'sum(case convert(varchar(10),rq,120) when '''+rq+''' then sl else 0 end) as ['+rq+']'
from (select distinct convert(varchar(10),rq,120) as rq from shuju) tselect @sql = 'select pm,'+@sql + ' from shuju group by pm'exec(@sql)
then
sl
else
0
end)
as '''+convert(varchar(10),rq,120)+'''
from shuju
效果如:
/*
'+convert(varchar(10),rq,120)+'
25
*/