摘要:本文主要向大家介绍了3种方法教你SQLServer数据库多行拼成一行,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
本文主要向大家介绍了3种方法教你SQLServer数据库多行拼成一行,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
运行环境 (sql 2000, 2005, 2008, 2014 ), 其中,最后一种方法 专为sql 2000提供。
原数据:
[sql] view plain copy
1. (5 行受影响)
2. UserID RoleName RoleID
3. ----------- ---------- --------
4. 2014000 developer 1
5. 2014000 product 2
6. 2014001 developer 1
7. 2014002 developer 1
8. 2014002 sales 3
期望结果:
[sql] view plain copy
1. UserID NewRoleName NewRoleID
2. ----------- ------------------ ------------
3. 2014000 developer, product 1|2
4. 2014001 developer 1
5. 2014002 developer, sales 1|3
解决方案:
原始数据脚本
[sql] view plain copy
1. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEST]') AND type in (N'U'))
2. DROP TABLE [dbo].[TEST]
3. GO
4.
5. create TABLE TEST(UserID INT,RoleName VARCHAR(100),RoleID INT)
6. INSERT INTO TEST
7. SELECT 2014000,'developer',1
8. UNION ALL SELECT 2014000,'product',2
9. UNION ALL SELECT 2014001,'developer',1
10. UNION ALL SELECT 2014002,'developer',1
11. UNION ALL SELECT 2014002,'sales',3
12.
13. -- SELECT * FROM TEST
14. -- SELECT * FROM TEST pivot( min(ROLEID) for USERID IN([2014000],[2014001],[2014002])) A
解决方法1:
[sql] view plain copy
1. SELECT t.UserID
2. ,STUFF((SELECT ','+ltrim(RoleName)
3. FROM TEST
4. WHERE UserID=t.UserID FOR XML PATH('')), 1, 1, '')
5. AS NewRoleName
6. ,STUFF((SELECT '|'+ltrim(RoleID)
7. FROM TEST
8. WHERE UserID=t.UserID FOR XML PATH('')), 1, 1, '')
9. AS NewRoleID
10. FROM TEST t
11. GROUP BY UserID
解决方法2:
[sql] view plain copy
1. SELECT A.*
2. ,STUFF(CONVERT(VARCHAR(100),C.RoleID),1,1,'') AS NewRoleID
3. ,REPLACE(
4. STUFF(CONVERT(VARCHAR(100),C.RoleName),1,1,'')
5. ,'|',', ') AS NewRoleName
6. FROM (
7. SELECT DISTINCT UserID
8. -- ,COUNT(DISTINCT ID) AS CountOfID
9. FROM TEST
10. GROUP BY UserID
11. ) A
12. CROSS APPLY (
13. SELECT RoleID = (
14. SELECT '|' + Convert(varchar(10),RoleID)
15. FROM TEST B
16. WHERE B.UserID = A.UserID
17. FOR XML PATH(''), TYPE
18. ),
19. RoleName = (
20. SELECT '|' + Convert(varchar(10),RoleName)
21. FROM TEST B
22. WHERE B.UserID = A.UserID
23. FOR XML PATH(''), TYPE
24. )
25. ) C
26.
解决方法3:
[sql] view plain copy
1. if object_id('F_RoleName') is not null
2. drop function F_RoleName
3. go
4. create function F_RoleName(@UserID VARCHAR(100))
5. returns nvarchar(100)
6. as
7. begin
8. declare @S nvarchar(100)
9. select @S=isnull(@S+', ','')+ RoleName from TEST where UserID=@UserID
10. return @S
11. end
12. go
13.
14.
15. if object_id('F_RoleID') is not null
16. drop function F_RoleID
17. go
18. create function F_RoleID(@UserID VARCHAR(100))
19. returns nvarchar(100)
20. as
21. begin
22. declare @S nvarchar(100)
23. select @S=isnull(@S+'|','')+ ltrim(RoleID) from TEST where UserID=@UserID
24. return @S
25. end
26. go
[sql] view plain copy
1. Select distinct UserID
2. ,NewRoleName=dbo.F_RoleName(UserID)
3. ,NewRoleID=dbo.F_RoleID(UserID)
4. from TEST
本文由职坐标整理并发布,了解更多内容,请关注职坐标SQL Server数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号