CODEWITHSUNDEEP
sqlsql-server
Pavel_K
Pavel_K

Reputation: 21

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

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?

Upvotes: 0

Views: 114

Answers (3)

hkravitz
hkravitz

Reputation: 1385

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

Upvotes: 0

Thom A
Thom A

Reputation: 96016

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.

Upvotes: 1

Abhishek
Abhishek

Reputation: 2490

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;

Upvotes: 0

Related Questions