drop table tbtest
go
create table tbtest(帐号 varchar(10),时间 datetime,项目 varchar(10),分数 int)
insert into tbtest
select '01','2007-4-4 10:10:11','01',5
union all select '01','2007-4-4 10:11:11','02',14
union all select '01','2007-4-4 10:13:11','01',7
union all select '02','2007-4-4 10:14:11','01',6
go
create proc up_test
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when 项目='''+项目+''' then 分数 else 0 end) as ''项目'+rtrim(cast(项目 as int))+''''
from(select distinct 项目 from tbtest)t
exec('select 帐号,convert(char(10),时间,120) as ''时间'''+@sql+' from (select * from tbtest
where 时间=(select max(时间) from tbtest a where a.帐号=tbtest.帐号 and a.项目=tbtest.项目))t group by 帐号,convert(char(10),时间,120)')
go
/*
帐号 时间 项目1 项目2
---------- ---------- ----------- -----------
01 2007-04-04 7 14
02 2007-04-04 6 0
*/
go
create table tbtest(帐号 varchar(10),时间 datetime,项目 varchar(10),分数 int)
insert into tbtest
select '01','2007-4-4 10:10:11','01',5
union all select '01','2007-4-4 10:11:11','02',14
union all select '01','2007-4-4 10:13:11','01',7
union all select '02','2007-4-4 10:14:11','01',6
go
create proc up_test
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when 项目='''+项目+''' then 分数 else 0 end) as ''项目'+rtrim(cast(项目 as int))+''''
from(select distinct 项目 from tbtest)t
exec('select 帐号,convert(char(10),时间,120) as ''时间'''+@sql+' from (select * from tbtest
where 时间=(select max(时间) from tbtest a where a.帐号=tbtest.帐号 and a.项目=tbtest.项目))t group by 帐号,convert(char(10),时间,120)')
go
/*
帐号 时间 项目1 项目2
---------- ---------- ----------- -----------
01 2007-04-04 7 14
02 2007-04-04 6 0
*/
(
帐号 VARCHAR(2),
时间 DATETIME,
项目 VARCHAR(2),
分数 INT
)
INSERT INTO TB
SELECT '01', '2007-4-4 10:10:11','01',5 UNION ALL
SELECT '01', '2007-4-4 10:11:11','02',14 UNION ALL
SELECT '01', '2007-4-4 10:13:11','01',7 UNION ALL
SELECT '02', '2007-4-4 10:14:11','01',6
GO
DECLARE @S VARCHAR(500)
SET @S='SELECT 帐号,MIN(CONVERT(CHAR(10),时间,120)) 时间,'
SELECT @S=@S+ 'MAX(CASE 项目 WHEN '''+项目+''' THEN 分数 ELSE 0 END) AS '''+项目+''',' FROM (SELECT DISTINCT 项目 FROM TB ) A
SET @S=STUFF(@S,LEN(@S),1,'')+' FROM TB GROUP BY 帐号'
exec( @S)
--结果
帐号 时间 01 02
---- ---------- ----------- -----------
01 2007-04-04 7 14
02 2007-04-04 6 0(2 行受影响)
SET @S='SELECT 帐号,MIN(CONVERT(CHAR(10),时间,120)) 时间,'
SELECT @S=@S+ 'MAX(CASE 项目 WHEN '''+项目+''' THEN 分数 ELSE 0 END) AS '''+项目+''',' FROM (SELECT DISTINCT 项目 FROM TB ) A
SET @S=STUFF(@S,LEN(@S),1,'')+' FROM TB GROUP BY 帐号'
print @S
然后把print的语句换成视图语句就成了。
或
set @s='create view viewtest as '+@S
exec(@s)
select 帐号,日期=convert(varchar(10),时间,120),
[项目1]=max(case 项目 when ' 项目1' then 分数 end),
[项目2]=max(case 项目 when ' 项目2' then 分数 end)
............
from table1
group by 帐号,convert(varchar(10),时间,120)