mssql 动态行转列。

mssql 动态行转列。

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 行受影响)

)

 

posted on 2016-11-21 20:40 zhouixi 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/1-Admin/p/6086861.html