declare @s varchar(8000)
set @s = 'select Name'
select
@s = @s+',['+a.checkdate+']=max(case when checkdate='''+a.checkdate+''' then rtrim(height)+'',''+rtrim(weight) end)'
from
(select distinct convert(char(10),checkdate,120) as checkdate from Table1)
set @s = @s + ' from Table1 group by Name'
exec(@s)
set @s = 'select Name'
select
@s = @s+',['+a.checkdate+']=max(case when checkdate='''+a.checkdate+''' then rtrim(height)+'',''+rtrim(weight) end)'
from
(select distinct convert(char(10),checkdate,120) as checkdate from Table1)
set @s = @s + ' from Table1 group by Name'
exec(@s)
create table Table1(name varchar(20),height int,weight int,checkdate datetime)
insert into Table1 select '张三',175,56,'2005-01-01'
insert into Table1 select '李四',172,28,'2005-01-01'
insert into Table1 select '王五',165,38,'2005-01-01'
insert into Table1 select '赵六',182,69,'2005-01-01'
insert into Table1 select '张三',176,57,'2005-02-02'
insert into Table1 select '李四',173,30,'2005-02-02'
insert into Table1 select '王五',165,36,'2005-02-02'
insert into Table1 select '赵六',186,68,'2005-02-02'
--执行交叉表查询
declare @s varchar(8000)
set @s = 'select Name'
select
@s = @s+',['+a.checkdate+']=max(case when checkdate='''+a.checkdate+''' then rtrim(height)+'',''+rtrim(weight) end)'
from
(select distinct convert(char(10),checkdate,120) as checkdate from Table1) aset @s = @s + ' from Table1 group by Name'
exec(@s)
--输出结果
/*
Name 2005-01-01 2005-02-02
---- ---------- ----------
李四 172,28 173,30
王五 165,38 165,36
张三 175,56 176,57
赵六 182,69 186,68
*/
--删除测试数据
drop table Table1
declare @date DateTime
declare cur_date Cursor For
select distinct checkdate as CheckDate from Table1
set @DSql = N' Select Name'
open cur_date
Fetch Next From cur_date into @date
WHILE @@FETCH_STATUS = 0
Begin
set @DSql = @DSql + N' ,Cast checkdate When ' + Cast(@date As Varchar(20)) + 'Then height as height,'
+ Cast checkdate When Cast(@date As Varchar(20)) +'Then weight as weight'
Fetch Next From cur_date into @date
end
Close cur_date
DEALLOCATE cur_date
EXECUTE sp_executesql @DSql
[create] table t1(name varchar(20),height int,weight int,checkdate datetime)
insert into t1 select '张三',175,56,'2005-01-01'
insert into t1 select '李四',172,28,'2005-01-01'
insert into t1 select '王五',165,38,'2005-01-01'
insert into t1 select '赵六',182,69,'2005-01-01'
insert into t1 select '张三',176,57,'2005-02-02'
insert into t1 select '李四',173,30,'2005-02-02'
insert into t1 select '王五',165,36,'2005-02-02'
insert into t1 select '赵六',186,68,'2005-02-02'declare @s varchar(2000)set @s=''
select @s=@s+',['+convert(char(10),checkdate,120)+']=max(case when checkdate='''+convert(char(10),checkdate,120)+''' then cast(height as varchar)
+'',''+cast(weight as varchar) end)'
from t1
group by checkdateselect @s='select name'+@s+' from t1 group by name'
exec(@s)drop table t1name 2005-01-01 2005-02-02
--------- --------------- ----------------
李四 172,28 173,30
王五 165,38 165,36
张三 175,56 176,57
赵六 182,69 186,68警告: 聚合或其它 SET 操作消除了空值。