Concatenate values in sql server and group by another value | ms sql server

Question

I have simple select

select distinct  UserName, Company from Users inner join Companies on Users.UserName = Companies.UserFullName

The result of query looks like this:

  User1 | Company1
  User1 | Company2
  User1 | Company3
  User1 | Company4
  User2 | Company3
  User2 | Company6
  User2 | Company1
  User2 | Company5

I want to concatenate Company values and group it by User. Like this:

  User1 | Company1 , Company2 , Company3 , Company4
  User2 | Company3 , Company6 , Company1 , Company5

Is it possible thing to do in sql server?

Yes, it is. What have you tried so far, and which version of SQL Server are you using? — Thom A, Feb 21 '18 at 08:59
Possible duplicate of [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — Valerica, Feb 21 '18 at 08:59
I'm using sql server 2014. At the moment I'm trying to use COALESCE. It works, but I can't group by correctly by User as there are still User duplicate values it query result — Pavel_K, Feb 21 '18 at 09:18
Possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Michał Turczyn, Feb 21 '18 at 09:22

score 1 · Answer 1 · answered Feb 21 '18 at 09:09

1

If you are using SQL Server 2017, you can use the new function, STRING_AGG:

SELECT UserName,
       STRING_AGG(Company,' , ') WITHIN GROUP (ORDER BY Company) AS Companies
FROM #T1
GROUP BY Username;

Note that you have no ordering in your table, thus the order of 'Company3, Company6, Company1, Company5' cannot be retained for 'User2' unless you have some other column to order by.

answered Feb 21 '18 at 09:09

Thom A

88,727
11
45
75

Wow. If you don't mind, tell me from where can I get myself updated with new t-sql functions in 2016, 2017? – Prabhat G Feb 21 '18 at 09:19
@PrabhatG Just have a [Google](https://www.google.co.uk/search?q=SQL+Server+2016+New+functions&ie=utf-8&oe=utf-8&client=firefox-b&gfe_rd=cr&dcr=0&ei=EjqNWorvO7TS8Afb5JGgBQ). Microsoft publish documents with every edition on what's new. The first (non-Ad) result in that link is to 2016's new features. – Thom A Feb 21 '18 at 09:23
I know that. Thought you've some blog or anything which puts up this info – Prabhat G Feb 21 '18 at 09:35
@PrabhatG No, i generally just have a look at the release notes when the new editions come out; but also pay attention to the mailing lists from sites like SQL Server Central and Brent Ozar. I'm sure there are blogs out there, however, I don't have any links personally. The documentation ([What's new in Database Engine - SQL Server 2017](https://learn.microsoft.com/en-gb/sql/database-engine/whats-new-in-sql-server-2017)) is quite thorough. – Thom A Feb 21 '18 at 09:38

score 0 · Answer 2 · answered Feb 21 '18 at 09:05

The below code snippet would work for you -

CREATE TABLE #t1 (UserName VARCHAR(100), Company VARCHAR(100));
INSERT #t1 values ('User1','Company1');
INSERT #t1 values ('User1','Company2');
INSERT #t1 values ('User1','Company3');
INSERT #t1 values ('User1','Company4');
INSERT #t1 values ('User2','Company3');
INSERT #t1 values ('User2','Company6');
INSERT #t1 values ('User2','Company1');
INSERT #t1 values ('User2','Company5');
GO
select
    UserName,
    stuff((
        select ',' + t.[Company]
        from #t1 t
        where t.UserName = #t1.UserName
        order by t.[Company]
        for xml path('')
    ),1,1,'') as CompanyName
from #t1
group by UserName;

hkravitz · Answer 3 · 2018-02-21T10:35:24.807

Another solution that doesn't require the clause FOR XML PATH This solution is a loop based

    SET NOCOUNT ON
    IF OBJECT_ID ('tempdb..#t1') IS NOT NULL DROP TABLE #T1;
    IF OBJECT_ID ('tempdb..#t2') IS NOT NULL DROP TABLE #T2;

    CREATE TABLE #t1 (UserName VARCHAR(100), Company VARCHAR(100));
    INSERT #t1 values ('User1','Company1');
    INSERT #t1 values ('User1','Company2');
    INSERT #t1 values ('User1','Company3');
    INSERT #t1 values ('User1','Company4');
    INSERT #t1 values ('User2','Company3');
    INSERT #t1 values ('User2','Company6');
    INSERT #t1 values ('User2','Company1');
    INSERT #t1 values ('User2','Company5');
    GO

    DECLARE @Table TABLE (UserName VARCHAR(100), Combined VARCHAR(4000))
    DECLARE @i INT = 1 

    SELECT DENSE_RANK () OVER (ORDER BY UserName) Seq,  *
    INTO #T2
    FROM #t1

    WHILE @i <= (SELECT MAX(Seq) FROM #T2)
        BEGIN 
        DECLARE @ConcatedCompany VARCHAR(4000) = ''
        SELECT @ConcatedCompany+= ',' + Company
        FROM #T2
        WHERE Seq = @i

        INSERT INTO @Table (UserName , Combined)
        SELECT UserName , STUFF(@ConcatedCompany,1,1,'')
        FROM #T2
        WHERE Seq = @i
        GROUP BY UserName 

        SET @i +=1

        END

    SELECT * 
    FROM @Table

UPDATE!!

Larnu's comment regarding the performance is a good point, usually I'd avoid using WHILE loops and think in terms of set based operations so, here is the solution without a loop and without "FOR XML PATH"

    SET NOCOUNT ON
    IF OBJECT_ID ('tempdb..#t1') IS NOT NULL DROP TABLE #T1
    IF OBJECT_ID ('tempdb..##T2') IS NOT NULL DROP TABLE ##T2
    IF OBJECT_ID ('tempdb..##Table') IS NOT NULL DROP TABLE ##Table

    CREATE TABLE #t1 (UserName VARCHAR(100), Company VARCHAR(100));
    INSERT #t1 values ('User1','Company1');
    INSERT #t1 values ('User1','Company2');
    INSERT #t1 values ('User1','Company3');
    INSERT #t1 values ('User1','Company4');
    INSERT #t1 values ('User2','Company3');
    INSERT #t1 values ('User2','Company6');
    INSERT #t1 values ('User2','Company1');
    INSERT #t1 values ('User2','Company5');
    GO

    CREATE TABLE ##Table (UserName nvarchar(50), Combined nvarchar(4000))

    SELECT DENSE_RANK () OVER (ORDER BY UserName) Seq,  *
    INTO ##T2
    FROM #t1

    DECLARE @cmd NVARCHAR(MAX) =''
    ;WITH T2 (Seq) AS 
    (
    SELECT DISTINCT Seq 
    FROM ##T2
    )


    SELECT @cmd += 'DECLARE @ConcatedCompany'+CONVERT(VARCHAR(10),Seq)+' NVARCHAR(4000) = '''' 
                    SELECT @ConcatedCompany'+CONVERT(VARCHAR(10),Seq)+' += '','' + Company FROM ##T2 WHERE Seq = '+CONVERT(VARCHAR(10),Seq)+ CHAR(10)+
                    ' INSERT INTO ##Table (UserName, Combined)
                    SELECT UserName , STUFF(@ConcatedCompany'+CONVERT(VARCHAR(10),Seq)+',1,1,'''') 
                    FROM ##T2 WHERE Seq = '+CONVERT(VARCHAR(10),Seq) + CHAR(10)+
                    ' GROUP BY UserName '+CHAR(10)+
                    ';'
                    +CHAR(10)
     FROM T2


     EXEC sp_executesql  @Cmd

     SELECT UserName , Combined 
     FROM ##Table

     DROP TABLE ##Table
     DROP TABLE ##T2

`WHILE` loops are just inherently slow in SQL Server. A Dataset approached is by far a more efficient method. — Thom A, Feb 21 '18 at 09:39
I agree, it can also be done in a set based without the need of loop, though in small cases (small number of users) the loop is not that bad.just wanted to note that a different solution other than the "FOR XML PATH" is available to consider. — hkravitz, Feb 21 '18 at 09:41

Concatenate values in sql server and group by another value | ms sql server

3 Answers3