请问有如下的原始表数据:
UserID GroupName
[email protected] 小组1
[email protected] 小组2
[email protected] 小组3
[email protected] 小组1
[email protected] 小组2要查询显示为
UserID GroupName
[email protected] 小组1 小组2 小组3
[email protected] 小组1 小组2
可以转换吗?
UserID GroupName
[email protected] 小组1
[email protected] 小组2
[email protected] 小组3
[email protected] 小组1
[email protected] 小组2要查询显示为
UserID GroupName
[email protected] 小组1 小组2 小组3
[email protected] 小组1 小组2
可以转换吗?
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
go
--> -->
if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([UserID] nvarchar(9),[GroupName] nvarchar(3))
Insert #
select N'[email protected]',N'小组1' union all
select N'[email protected]',N'小组2' union all
select N'[email protected]',N'小组3' union all
select N'[email protected]',N'小组1' union all
select N'[email protected]',N'小组2'
Go
Select [UserID],
[GroupName]=STUFF((SELECT ' '+[GroupName] FROM # WHERE UserID=a.UserID FOR XML PATH('')),1,1,'')
from # AS a GROUP BY [UserID]
/*
UserID GroupName
[email protected] 小组1 小组2 小组3
[email protected] 小组1 小组2
*/
FROM TB WHERE T.UserID=UserID ORDER BY GroupName
FOR XML PATH('')
FROM TB T
GROUP BY UserID
字符串串联..
insert into tb select '[email protected]','小组1'
insert into tb select '[email protected]','小组2'
insert into tb select '[email protected]','小组3'
insert into tb select '[email protected]','小组1'
insert into tb select '[email protected]','小组2'
go
select userid,stuff((select ' '+groupname from tb where userid=a.userid for xml path('')),1,1,'')
from tb a group by userid
go
drop table tb
/*
userid
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[email protected] 小组1 小组2 小组3
[email protected] 小组1 小组2(2 行受影响)
*/