create table #a (a int , b char(4))
insert into #a select 1,'张三'
insert into #a select 2,'李四'
insert into #a select 3,'王五'
select * from #a
a b
----------- ----
1 张三
2 李四
3 王五
(3 行受影响)
--行转列，步骤：''+张三+],[+王五+],[+李四
declare @sql varchar(100)
select @sql=isnull(@sql+'],[','')+b from #a
select @sql='['+@sql+']'
select @sql as 结果1
go
结果1
----------------------------------------------------------------------------------------------------
[张三],[李四],[王五]
(1 行受影响)
--结果为NULL，所以用ISNULL
declare @sql varchar(100)
select @sql=@sql+b from #a
select @sql
NULL
----------------------------------------------------------------------------------------------------
NULL
(1 行受影响)
--结果，同上
declare @sql varchar(100)
set @sql=''
select @sql=@sql+'],['+b from #a
select @sql=right(@sql,len(@sql)-2)+']'
select @sql as 结果2
go
结果2
----------------------------------------------------------------------------------------------------
[张三],[李四],[王五]
(1 行受影响)
--结果，同上
declare @sql varchar(100)
set @sql=''
select @sql=stuff((select '],['+b from #a for xml path('')),1,2,'') +']'
select @sql as 结果3
go
结果3
----------------------------------------------------------------------------------------------------
[张三],[李四],[王五]
(1 行受影响)
--结果，同上。
declare @sql varchar(8000)
select @sql=coalesce(@sql+'],[','')+b from #a
select @sql='['+@sql+']'
select @sql as 结果4
go
结果4
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[张三],[李四],[王五]
(1 行受影响)
--思路一样都是列行互转，结果不一样。
declare @b varchar(100)
declare @c varchar(100)
set @c='select * from (select b from #a ) as e pivot (max(b) for b in ([张三],[李四],[王五])) as c'
exec(@c)
张三 李四 王五
---- ---- ----
张三 李四 王五
(1 行受影响)
create table #a
(
id char(2) ,
a char(2),
b char(2)
)
insert into #a values('1','2','3')
insert into #a values('1','3','3')
insert into #a values('1','4','3')
insert into #a values('2','1','5')
insert into #a values('2','2','5')
select a.id,
[a]=stuff((select ',' +b from #a as a1 where a1.id=a.id FOR XML PATH('')),1,1,' ' )
,b
from #a a
group by a.id,b;
drop table #a;
结果：
id a b
----------------
1 3,3,3 3
2 5,5 5
(2 行受影响)
)
