create table a(ID int, col1 int, Name nvarchar(5))
insert a select 1, 1, 'a'
union all select 1, 2, 'b'
union all select 1, 3, 'c'
union all select 2, 1, 'd'
union all select 2, 2, 'e'
union all select 2, 3, 'f'
union all select 3, 1, 'h'
union all select 3, 2, 'i'
union all select 3, 3, 'j'
godeclare @s nvarchar(2000)
set @s=''
select @s=@s+',Name'+rtrim(col1)+'= Max(case col1 when '+rtrim(col1)+' then Name else '''' end)'
from a group by col1
exec('select ID'+@s+' from a group by ID')--drop table a
D Name1 Name2 Name3
----------- ----- ----- -----
1 a b c
2 d e f
3 h i j
insert a select 1, 1, 'a'
union all select 1, 2, 'b'
union all select 1, 3, 'c'
union all select 2, 1, 'd'
union all select 2, 2, 'e'
union all select 2, 3, 'f'
union all select 3, 1, 'h'
union all select 3, 2, 'i'
union all select 3, 3, 'j'
godeclare @s nvarchar(2000)
set @s=''
select @s=@s+',Name'+rtrim(col1)+'= Max(case col1 when '+rtrim(col1)+' then Name else '''' end)'
from a group by col1
exec('select ID'+@s+' from a group by ID')--drop table a
D Name1 Name2 Name3
----------- ----- ----- -----
1 a b c
2 d e f
3 h i j
declare @s nvarchar(2000)
select @s=isnull(@s+',','')+'Name'+rtrim(col1)+'= Max(case col1 when '+rtrim(col1)+' then Name else '''' end)'
from a group by col1
exec('select '+@s+' from a group by ID')go
Name1 Name2 Name3
----- ----- -----
a b c
d e f
h i j
select
Name1= Max(case col1 when 1 then Name else '' end),
Name2= Max(case col1 when 2 then Name else '' end),
Name3= Max(case col1 when 3 then Name else '' end)
from
a
group by ID
我也贴一个
---------------------------------------------------------
Declare @sql Varchar(1000)
Set @sql=''
Select @sql=@sql+',Max(Case 列 When '''+rtrim(列)+''' Then 值 Else '''' End) As ['+rtrim(列) +']'
From T Group By 列
Set @sql=Stuff(@sql,1,1,'')
Exec('Select '+@sql+'From T Group By 行')
insert into tb values(1, 1, 'a' )
insert into tb values(1, 2, 'b' )
insert into tb values(1, 3, 'c' )
insert into tb values(2, 1, 'd' )
insert into tb values(2, 2, 'e' )
insert into tb values(2, 3, 'f' )
insert into tb values(3, 1, 'h' )
insert into tb values(3, 2, 'i' )
insert into tb values(3, 3, 'j' )
go
select 行,
max(case when 列 = 1 then 值 end) col1,
max(case when 列 = 2 then 值 end) col2,
max(case when 列 = 3 then 值 end) col3
from tb
group by 行
drop table tb/*
行 col1 col2 col3
----------- ---------- ---------- ----------
1 a b c
2 d e f
3 h i j
(所影响的行数为 3 行)
*/
insert into tb values(1, 1, 'a' )
insert into tb values(1, 2, 'b' )
insert into tb values(1, 3, 'c' )
insert into tb values(2, 1, 'd' )
insert into tb values(2, 2, 'e' )
insert into tb values(2, 3, 'f' )
insert into tb values(3, 1, 'h' )
insert into tb values(3, 2, 'i' )
insert into tb values(3, 3, 'j' )
go
select col1,col2,col3 from
(
select 行,
max(case when 列 = 1 then 值 end) col1,
max(case when 列 = 2 then 值 end) col2,
max(case when 列 = 3 then 值 end) col3
from tb
group by 行
) t
drop table tb/*
col1 col2 col3
---------- ---------- ----------
a b c
d e f
h i j
(所影响的行数为 3 行)
*/
insert into tb values(1, 1, 'a' )
insert into tb values(1, 2, 'b' )
insert into tb values(1, 3, 'c' )
insert into tb values(2, 1, 'd' )
insert into tb values(2, 2, 'e' )
insert into tb values(2, 3, 'f' )
insert into tb values(3, 1, 'h' )
insert into tb values(3, 2, 'i' )
insert into tb values(3, 3, 'j' )
goselect t1.值 col1,t2.值 col2,t3.值 col3 from
(select * from tb where 列 = 1) t1,
(select * from tb where 列 = 2) t2,
(select * from tb where 列 = 3) t3
where t1.行 = t2.行 and t1.行 = t3.行drop table tb/*
col1 col2 col3
---------- ---------- ----------
a b c
d e f
h i j
(所影响的行数为 3 行)
*/