22,209
社区成员
发帖
与我相关
我的任务
分享
create table tb(col1 varchar(10), col2 varchar(10),col3 varchar(10))
insert into tb values('A' , '10' , '2008-01-12')
insert into tb values('A' , '11' , '2008-01-15')
insert into tb values('A' , '11' , '2008-02-01')
insert into tb values('A' , '11' , '2008-02-20')
insert into tb values('A' , '12' , '2008-02-23')
insert into tb values('A' , '13' , '2008-03-01')
insert into tb values('A' , '13' , '2008-03-11')
insert into tb values('A' , '13' , '2008-03-21')
insert into tb values('A' , '9' , '2008-05-15')
insert into tb values('A' , '7.5' , '2008-07-15')
go
declare @sql varchar(8000)
set @sql = 'select col1 '
select @sql = @sql + ' , max(case col2 when ''' + cast(col2 as varchar) + ''' then col3 else '''' end) [' + cast(col2 as varchar) + ']'
from (select distinct col2 from tb) as a
set @sql = @sql + ' from tb group by col1'
exec(@sql)
drop table tb
/*
col1 10 11 12 13 7.5 9
---------- ---------- ---------- ---------- ---------- ---------- ----------
A 2008-01-12 2008-02-20 2008-02-23 2008-03-21 2008-07-15 2008-05-15
*/
IF NOT OBJECT_ID('TB') IS NULL DROP TABLE TB
GO
CREATE TABLE TB([ANAME] NVARCHAR(1),[ANUM] DECIMAL(18,1),[ADATE] DATETIME)
Insert TB
SELECT N'A',10,'2008-01-12' UNION ALL
SELECT N'A',11,'2008-01-15' UNION ALL
SELECT N'A',11,'2008-02-01' UNION ALL
SELECT N'A',11,'2008-02-20' UNION ALL
SELECT N'A',12,'2008-02-23' UNION ALL
SELECT N'A',13,'2008-03-01' UNION ALL
SELECT N'A',13,'2008-03-11' UNION ALL
SELECT N'A',13,'2008-03-21' UNION ALL
SELECT N'A',9,'2008-05-15' UNION ALL
SELECT N'A',7.5,'2008-07-15'
Go
SELECT * FROM TB
DECLARE @S nvarchar(4000)
SET @S='SELECT ANAME'
SELECT @S=@S+','+QUOTENAME(ANUM)
+N'=CONVERT(VARCHAR(10),MIN(CASE ANUM WHEN '+QUOTENAME(ANUM,N'''')
+N' THEN ADATE END),120)'
FROM TB
GROUP BY ANUM
EXEC (@S+N'FROM TB GROUP BY ANAME')
/*
ANAME 7.5 9.0 10.0 11.0 12.0 13.0
----- ---------- ---------- ---------- ---------- ---------- ----------
A 2008-07-15 2008-05-15 2008-01-12 2008-01-15 2008-02-23 2008-03-01
*/