交叉表问题

来源：百度知道 编辑：UC知道 时间：2024/06/23 04:52:50
创建表：
create table _strms
(id varchar(4) not null,
date_ datetime null,
value_ real null)
插入值：
INSERT INTO _strms (id,date_,value_) VALUES ('a','2009-7-1 0:00:00','100')
INSERT INTO _strms (id,date_,value_) VALUES ('a','2009-7-2 0:00:00','101')
INSERT INTO _strms (id,date_,value_) VALUES ('b','2009-7-1 0:00:00','102')
INSERT INTO _strms (id,date_,value_) VALUES ('b','2009-7-2 0:00:00','103')
INSERT INTO _strms (id,date_,value_) VALUES ('c','2009-7-1 0:00:00','104')
INSERT INTO _strms (id,date_,value_) VALUES ('c','2009-7-2 0:00:00','105')
INSERT INTO _strms (id,date_,value_) VALUES ('d','2009-7-1 0:00:00','106')
INSERT INTO _strms (id,date_,value_) VALUES ('d','2009-7-2 0:00:00','107')
INSERT INTO _strms (id,date_,value_) VALUES

declare @sql varchar(8000)
declare @date1 varchar(50)
declare @date2 varchar(50)
set @date1='2009-6-3 0:00:00'
set @date2='2009-7-3 0:00:00'
select @sql = 'select ID,'
select @sql = @sql + 'sum(case CONVERT(CHAR(10),date_,121) when '''
+CONVERT(CHAR(10),date_,121)+''' then value_ else 0 end) as '''+CONVERT(CHAR(10),date_,121)+''' ,'
from (select distinct date_ from _STRMS where date_ between @date1 and @date2) as a
select @sql = left(@sql,len(@sql)-1) + '
from _STRMS group by ID'
exec(@sql)

select id, max(case when date_='2009-7-1' then value_ else 0 end) ddd,max(case when date_='2009-7-2' then value_ else 0 end) ddd from _strms group by id;