转换问题
现在结构
行 列 值
1 1 a
1 2 b
1 3 c
2 1 d
2 2 e
2 3 f
3 1 h
3 2 i
3 3 j
转换以后变成下面的表
a,b,c
d,e,f
h,i,j
[解决办法]
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 '
go
declare @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
[解决办法]
Roy兄,健身回来了啊?呵呵
我也贴一个
---------------------
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 行 ')
[解决办法]
create table tb(行 int, 列 int, 值 varchar(10))
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 行）
*/
[解决办法]
create table tb(行 int, 列 int, 值 varchar(10))
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 行）
*/
[解决办法]
create table tb(行 int, 列 int, 值 varchar(10))
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 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 行）
*/
