Multiple lines of SQL into one line (3 solutions)
The runtime Environment (SQL 2000, 2005, 2008, 2014), where the last method is specifically provided for SQL 2000.
Original data:
(5 rows affected) UserID RoleName roleid--------------------- --------2014000 developer 12014000 Product 22014001 Developer 12014002 developer 12014002 Sales 3
Expected Result:
UserID newrolename newroleid----------------------------- ------------2014000 Developer, Product 1|22014001 developer 12014002 Developer, sales 1|3
Solution:
Raw Data Script
IF EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ TEST] ') and type in (N ' U ') DROP TABLE [dbo]. [TEST] Gocreate TABLE TEST (UserID int,rolename VARCHAR (+), Roleid INT) INSERT into Testselect 2014000, ' Developer ', 1UNION all Select 2014000, ' product ', 2UNION all Select 2014001, ' Developer ', 1UNION all Select 2014002, ' Developer ', 1UNION all Select 2014002, ' Sales ', 3--select * FROM Test--select * FROM Test pivot (min (Roleid) to USERID in ([2014000],[2014001],[2014002 ])) A
Workaround 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 Newroleidfrom TEST Tgroup by UserID
Workaround 2:
SELECT a.*,stuff (CONVERT (varchar), C.roleid), +, ") as Newroleid,replace (STUFF (CONVERT (varchar), C. RoleName), "("), ","), ' | ', ', ') as Newrolenamefrom (SELECT DISTINCT UserID--, COUNT (DISTINCT ID) as Countofid from TEST GROUP by UserID) ACROSS APPLY (Select Roleid = (= [] + Convert (varchar), Roleid) from TEST bwhere B.userid = A.USERIDFO R XML PATH ("), TYPE ), RoleName = (" SELECT ' | ' + Convert (varchar), RoleName) from TEST bwhere B.userid = a.useridfor X ML PATH ("), TYPE
Workaround 3:
If object_id (' F_rolename ') is not null drop function f_rolename go create function f_rolename (@UserID VARCHAR) re Turns nvarchar (+) as begin declare @S nvarchar (+) Select @s=isnull (@s+ ', ', ') + RoleName from TEST where [emai L protected] return @S end Go if object_id (' F_roleid ') is not null drop function F_roleid go create function F_rol EID (@UserID VARCHAR) returns nvarchar ( +) as begin declare @S nvarchar (+) Select @s=isnull (@s+ ' | ', ') + LTrim (Roleid) from TEST where [email protected]
Multiple lines of SQL into one line (3 solutions)