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