Need to Combine column values from multiple rows into single row in SQL

Question

For this query i am getting similar rows with multiple values. Need to combine values within single row. I tried using Stuff but i am unable to do so. Need to organise data with only SQL functions and no Pl/SQL code if possible.

STUFF((SELECT '| ' + udn1.DisplayName 
          FROM vUserDisplayName udn1
          FOR XML PATH('')), 1, 1, '') [USERS]

SELECT  gdn.DisplayName,
            ,ISNULL(udn.DisplayName, N'No user assigned') AS [User]
    
        FROM tbGroup g 
            INNER JOIN vGroupDisplayName gdn
                ON g.GroupId = gdn.GroupId
            LEFT JOIN tbUserGroup ug
                ON g.GroupId = ug.GroupId
            LEFT JOIN tbUser u
                ON ug.UserId = u.UserId 
            LEFT JOIN vUserDisplayName udn
                ON u.UserId = udn.UserId 
        WHERE
            (u.[Enabled] = 1 OR u.UserId IS NULL)
            AND
            g.IsPersonal = 0
            AND
            g.SystemGroup = 0

Output :

DisplayName                User
a                     [email protected]
a                     [email protected]
a                     [email protected]
b                     [email protected]
b                     [email protected]
b                     [email protected]
c                     [email protected]
c                     [email protected]
c                     [email protected]

Expected Output :

DisplayName                User
a                     [email protected]|[email protected]|[email protected]                  
b                     [email protected]|[email protected]|[email protected]              
c                     [email protected]|[email protected]|[email protected]

Yes it is sql-server syntax. sorry by mistake i have added mysql in tags. — Sunny
– Sunny, Commented May 5, 2021 at 18:56

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-05-05 19:50:13Z

1

For SQL Server 2016:

Using common table expression:

With cte as
(
        SELECT  gdn.DisplayName,
            ISNULL(udn.DisplayName, N'No user assigned') AS [User]
    
        FROM tbGroup g 
            INNER JOIN vGroupDisplayName gdn
                ON g.GroupId = gdn.GroupId
            LEFT JOIN tbUserGroup ug
                ON g.GroupId = ug.GroupId
            LEFT JOIN tbUser u
                ON ug.UserId = u.UserId 
            LEFT JOIN vUserDisplayName udn WITH (NOLOCK)
                ON u.UserId = udn.UserId 
        WHERE
            (u.[Enabled] = 1 OR u.UserId IS NULL)
            AND
            g.IsPersonal = 0
            AND
            g.SystemGroup = 0
)
       SElecT displayname, STUFF((SELECT  '|' + ([user])
            FROM cte tt where  tt.displayname=t.displayname
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        from cte t group by displayname

Using subquery:

   SElecT displayname, STUFF((SELECT  '|' + ([user])
        FROM ( SELECT  gdn.DisplayName,
        ISNULL(udn.DisplayName, N'No user assigned') AS [User]

    FROM tbGroup g 
        INNER JOIN vGroupDisplayName gdn
            ON g.GroupId = gdn.GroupId
        LEFT JOIN tbUserGroup ug
            ON g.GroupId = ug.GroupId
        LEFT JOIN tbUser u
            ON ug.UserId = u.UserId 
        LEFT JOIN vUserDisplayName udn WITH (NOLOCK)
            ON u.UserId = udn.UserId 
    WHERE
        (u.[Enabled] = 1 OR u.UserId IS NULL)
        AND
        g.IsPersonal = 0
        AND
        g.SystemGroup = 0) tt where  tt.displayname=t.displayname
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
    from ( SELECT  gdn.DisplayName,
        ISNULL(udn.DisplayName, N'No user assigned') AS [User]

    FROM tbGroup g 
        INNER JOIN vGroupDisplayName gdn
            ON g.GroupId = gdn.GroupId
        LEFT JOIN tbUserGroup ug
            ON g.GroupId = ug.GroupId
        LEFT JOIN tbUser u
            ON ug.UserId = u.UserId 
        LEFT JOIN vUserDisplayName udn WITH (NOLOCK)
            ON u.UserId = udn.UserId 
    WHERE
        (u.[Enabled] = 1 OR u.UserId IS NULL)
        AND
        g.IsPersonal = 0
        AND
        g.SystemGroup = 0) t group by displayname

You can use string_agg() for sql server 2017 and onward versions.

  SELECT  gdn.DisplayName,
            string_agg(ISNULL(udn.DisplayName, N'No user assigned'),'|') AS [User]
    
        FROM tbGroup g 
            INNER JOIN vGroupDisplayName gdn
                ON g.GroupId = gdn.GroupId
            LEFT JOIN tbUserGroup ug
                ON g.GroupId = ug.GroupId
            LEFT JOIN tbUser u
                ON ug.UserId = u.UserId 
            LEFT JOIN vUserDisplayName udn WITH (NOLOCK)
                ON u.UserId = udn.UserId 
        WHERE
            (u.[Enabled] = 1 OR u.UserId IS NULL)
            AND
            g.IsPersonal = 0
            AND
            g.SystemGroup = 0
        group by gdn.DisplayName

Fiddle example:

 create table testtable( DisplayName varchar(10), [User] varchar(50));
 insert into testtable values('a',                     '[email protected]');
 insert into testtable values('a',                     '[email protected]');
 insert into testtable values('a',                     '[email protected]');
 insert into testtable values('b',                     '[email protected]');
 insert into testtable values('b',                     '[email protected]');
 insert into testtable values('b',                     '[email protected]');
 insert into testtable values('c',                    '[email protected]');
 insert into testtable values('c',                    '[email protected]');
 insert into testtable values('c',                     '[email protected]');

Query:

 select displayname,string_agg([User],'|') as [User] from testtable
 group by displayname
 GO

Output:

displayname	User
a	[email protected]	[email protected]
b	[email protected]	[email protected]
c	[email protected]	[email protected]

db<fiddle here

edited May 5, 2021 at 19:50

answered May 5, 2021 at 19:01

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Sunny Over a year ago

Its really strange. STRING_AGG is showing as in-built function as recommendation but when i use that, it says its not in-built function and throwing error as : Msg 195, Level 15, State 10, Line 1 'STRING_AGG' is not a recognized built-in function name. Msg 102, Level 15, State 1, Line 20 Incorrect syntax near 't'.

Kazi Mohammad Ali Nur Romel Over a year ago

Please execute select @@VERSION and share the result.

Sunny Over a year ago

My Bad : i am new to all this. Its V16 Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

Kazi Mohammad Ali Nur Romel Over a year ago

SQL server 2016 doesn't support string_agg()

Kazi Mohammad Ali Nur Romel Over a year ago

Stuff() with XML Path For() as you mentioned in your question. I have just revised my answer

|

Collectives™ on Stack Overflow

Need to Combine column values from multiple rows into single row in SQL

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related