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