SQL结果集转换--附多行转为一列

如下图将结果集1(图1)转换为结果集2(图2)

 

ID          name                 value
----------- -------------------- ----------
1           张三                   aaa
2           张三                   ssss
3           张三                   ddd
4           张三                   fff
5           李四                   ggg
6           李四                   hhhh
7           李四                   jjj
8           李四                   kkk
9           李四                   ll

              

              图1

 

name                 Two
-------------------- ---------------------------

李四                   ggg,hhhh,jjj,kkk,ll
张三                   aaa,ssss,ddd,fff

              图2

 

SQL语句：

   use City; go SET NOCOUNT ON if OBJECT_ID(N'A',N'U') is not null drop table A go create table A ( ID int identity(1,1) primary key not null, name nvarchar(20), value nvarchar(10) ) go insert into A select '张三', 'aaa' union all select '张三','ssss' union all select '张三', 'ddd' union all select '张三', 'fff' union all select '李四', 'ggg' union all select '李四', 'hhhh' union all select '李四', 'jjj' union all select '李四', 'kkk' union all select '李四', 'll' go with One as ( select *,(value) as Re from A ) select name, --这里本来可以用substring函数可以实现的，但是substring函数要获取两次下面的结果集。于是用stuff，可见，在 --SQL中如要用Substring要计算两次结果集的时候就考虑用stuff。 --再者，用for xml path的是很默认生成的每个节点的名称是根据列名和列名的别名。那么不给这个选择字段列名或列名 --别名呢？那么生成的xml字符串就没有那种节点出现了。这是关键 stuff((select ','+Re from One where One.name=A.name for xml path('')),1,1,'') as Two from A group by name drop table A --可以看出，用这种方法可以将【多行中的一列转为一行】。

并且利用 for xml path 可以将多行中的一列以任意效果显示！如：

name                 Two
-------------------- --------------------------------------

李四                   {ggg},{hhhh},{jjj},{kkk},{ll}
张三                   {aaa},{ssss},{ddd},{fff}

等等。

 

将上面的结果集反止：

use City;
go
Set nocount on
if object_id('tb') is not null
drop table tb
go
create table tb([name] nvarchar(4),[value] nvarchar(9))
insert tb
select '张三','1,2,3,4' union all
select '李四','a,s,d,f,g'
go
SELECT a.[name],b.[value]
FROM (SELECT [name],[value]=CAST('<v>'+REPLACE([value],',','</v><v>')+'</v>' AS xml) FROM tb) a
OUTER APPLY (SELECT [value]=T.C.value('.','varchar(50)') FROM a.[value].nodes('/v') AS T(C)) b

就由原来的：

name value
---- ---------
张三   1,2,3,4
李四   a,s,d,f,g

转为：

name value
---- ------
张三   1
张三   2
张三   3
张三   4
李四   a
李四   s
李四   d
李四   f
李四   g