SQL 多行拼成一行 (3种解决方案)

SQL 多行拼成一行 (3种解决方案)

运行环境 (sql 2000, 2005, 2008, 2014 ), 其中，最后一种方法 专为sql 2000提供。

原数据：

(5 行受影响)
UserID      RoleName          RoleID
----------- ----------       --------
2014000     developer           1
2014000     product             2
2014001     developer           1
2014002     developer           1
2014002     sales               3

期望结果：

UserID      NewRoleName                NewRoleID
----------- ------------------        ------------ 
2014000     developer, product          1|2
2014001     developer                   1
2014002     developer, sales            1|3

解决方案：

原始数据脚本

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEST]') AND type in (N'U'))
DROP TABLE [dbo].[TEST]
GO

create TABLE TEST(UserID INT,RoleName VARCHAR(100),RoleID INT)
INSERT INTO TEST
SELECT 2014000,'developer',1
UNION ALL SELECT 2014000,'product',2
UNION ALL SELECT 2014001,'developer',1
UNION ALL SELECT 2014002,'developer',1
UNION ALL SELECT 2014002,'sales',3

-- SELECT * FROM TEST 
-- SELECT * FROM TEST pivot( min(ROLEID) for USERID IN([2014000],[2014001],[2014002])) A

解决方法1：

SELECT t.UserID
	,STUFF((SELECT ','+ltrim(RoleName)
                    FROM TEST  
                    WHERE UserID=t.UserID FOR XML PATH('')), 1, 1, '') 
	AS NewRoleName
	,STUFF((SELECT '|'+ltrim(RoleID)
                    FROM TEST  
                    WHERE UserID=t.UserID FOR XML PATH('')), 1, 1, '') 
	AS NewRoleID
FROM TEST t
GROUP BY UserID

解决方法2：

SELECT A.*
	,STUFF(CONVERT(VARCHAR(100),C.RoleID),1,1,'') AS NewRoleID
	,REPLACE(
		STUFF(CONVERT(VARCHAR(100),C.RoleName),1,1,'')
		,'|',', ') AS NewRoleName
FROM (
	 SELECT DISTINCT UserID
		-- ,COUNT(DISTINCT ID) AS CountOfID
	 FROM TEST
	 GROUP BY UserID
) A
CROSS APPLY (
	 SELECT RoleID = (
		SELECT '|' + Convert(varchar(10),RoleID)
		FROM TEST B
		WHERE B.UserID = A.UserID
		FOR XML PATH(''), TYPE
	   ),
		RoleName = (
		SELECT '|' + Convert(varchar(10),RoleName)
		FROM TEST B
		WHERE B.UserID = A.UserID
		FOR XML PATH(''), TYPE
	   )
) C
 

解决方法3：

if object_id('F_RoleName') is not null 
    drop function F_RoleName 
go 
create function F_RoleName(@UserID VARCHAR(100)) 
returns nvarchar(100) 
as 
begin 
    declare @S nvarchar(100) 
    select @S=isnull(@S+', ','')+ RoleName from TEST where UserID=@UserID 
    return @S 
end 
go 


if object_id('F_RoleID') is not null 
    drop function F_RoleID 
go 
create function F_RoleID(@UserID VARCHAR(100)) 
returns nvarchar(100) 
as 
begin 
    declare @S nvarchar(100) 
    select @S=isnull(@S+'|','')+ ltrim(RoleID) from TEST where UserID=@UserID 
    return @S 
end 
go 

Select distinct UserID
	,NewRoleName=dbo.F_RoleName(UserID) 
	,NewRoleID=dbo.F_RoleID(UserID) 
from TEST