我的数据库中有这样的一张表
FACTOR YIELD DATE
工厂1 1000 2010-07-01
工厂2 1100 2010-07-01
工厂3 900 2010-07-01
工厂1 4000 2010-07-02
工厂2 1000 2010-07-02
工厂3 1000 2010-07-02
工厂1 2000 2010-07-03
工厂2 3000 2010-07-03
工厂3 1000 2010-07-02
. . .
. . .
我现在需要得到这样的数据
FACTOR 2010-07-01 2010-07-02 2010-07-03 . . . . .
工厂1 1000 4000 2000 . . . . .
工厂2 1100 1000 3000 . . . . .
工厂3 900 1000 1000 . . . . .
这个我要得到的列的个数是由天数来决定的,不知道这样怎么用SQL语句来实现?
FACTOR YIELD DATE
工厂1 1000 2010-07-01
工厂2 1100 2010-07-01
工厂3 900 2010-07-01
工厂1 4000 2010-07-02
工厂2 1000 2010-07-02
工厂3 1000 2010-07-02
工厂1 2000 2010-07-03
工厂2 3000 2010-07-03
工厂3 1000 2010-07-02
. . .
. . .
我现在需要得到这样的数据
FACTOR 2010-07-01 2010-07-02 2010-07-03 . . . . .
工厂1 1000 4000 2000 . . . . .
工厂2 1100 1000 3000 . . . . .
工厂3 900 1000 1000 . . . . .
这个我要得到的列的个数是由天数来决定的,不知道这样怎么用SQL语句来实现?
if object_id('tempdb.dbo.#a') is not null drop table #a
go
create table #a (FACTOR varchar(5),YIELD int,DATE datetime)
insert into #a
select '工厂1',1000,'2010-07-01' union all
select '工厂2',1100,'2010-07-01' union all
select '工厂3',900,'2010-07-01' union all
select '工厂1',4000,'2010-07-02' union all
select '工厂2',1000,'2010-07-02' union all
select '工厂3',1000,'2010-07-02' union all
select '工厂1',2000,'2010-07-03' union all
select '工厂2',3000,'2010-07-03' union all
select '工厂3',1000,'2010-07-02'declare @s nvarchar(4000)
select @s=isnull(@s+',','')+'sum(case when DATE='''+ltrim(DATE)+''' then YIELD else 0 end )as '''+convert(char(10),DATE,120)+'''' from (select distinct (DATE) from #a)tselect @s='select FACTOR,'+@s +' from #a group by FACTOR'
exec(@s)
/*
(所影响的行数为 9 行)FACTOR 2010-07-01 2010-07-02 2010-07-03
------ ----------- ----------- -----------
工厂1 1000 4000 2000
工厂2 1100 1000 3000
工厂3 900 2000 0
http://blog.csdn.net/xys_777/archive/2010/06/22/5685953.aspx
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([FACTOR] [nvarchar](10),[YIELD] [int],[DATE] [datetime])
INSERT INTO [tb]
SELECT '工厂1','1000','2010-07-01' UNION ALL
SELECT '工厂2','1100','2010-07-01' UNION ALL
SELECT '工厂3','900','2010-07-01' UNION ALL
SELECT '工厂1','4000','2010-07-02' UNION ALL
SELECT '工厂2','1000','2010-07-02' UNION ALL
SELECT '工厂3','1000','2010-07-02' UNION ALL
SELECT '工厂1','2000','2010-07-03' UNION ALL
SELECT '工厂2','3000','2010-07-03' UNION ALL
SELECT '工厂3','1000','2010-07-02'--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @s VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME(CONVERT(CHAR(10),DATE,23))
FROM tb
GROUP BY CONVERT(CHAR(10),DATE,23)
EXEC('
SELECT *
FROM (
SELECT FACTOR,YIELD,CONVERT(CHAR(10),DATE,23) DATE
FROM tb
) a
PIVOT(SUM(YIELD) FOR DATE IN('+@s+')) b
')
/*
FACTOR 2010-07-01 2010-07-02 2010-07-03
---------- ----------- ----------- -----------
工厂1 1000 4000 2000
工厂2 1100 1000 3000
工厂3 900 2000 NULL(3 行受影响)
*/