Combine Multiple Columns And Records In MS SQL Server

Combine Multiple Columns And Records Into Comma Separated One Column In MS SQL Server. In this example i am going to describe how to combine multiple columns and records in one column in MS SQL.

Here is the scenario

I have a table having Employees names and their respective Department names,
now i want to show Employees names separated by comma into one column and respective Department name in another column.



My table schema is shown in the image below

Combine Multiple Columns And Records Sql Server

And this is Data into table



I want output in following format

                                  Department                               FirstName
                                   IT                                             amiT,Emp1,Emp5
                                  Admin                                       Shobhit, Emp3,Emp7

and so on

To get this desired result we need to write below mentioned query

SELECT DISTINCT
Department,
EmpNames = substring( ( SELECT ', ' + FirstName 
FROM Employees e2
WHERE e2.Department = e1.Department FOR XML path(''), elements 
),2,500)
FROM Employees e1

And the output of this SQL Query would be



Hope this helps


20 comments:

  1. AnonymousJuly 29, 2009 at 2:03 AM

    It help me. Thanks.

    I've changed "substring((SELECT ...), 2,500)" to "stuff((SELECT ...), 1, 1, '')" so it's not tied to the varchar length.

    ReplyDelete
  2. AnonymousJanuary 14, 2010 at 5:46 PM

    substring((SELECT ...), 2,500)" to "stuff((SELECT ...), 1, 1, '')"

    why not work?

    please read this
    Syntax:
    SUBSTRING ( value_expression ,start_expression , length_expression )
    your length_expression = 1

    ReplyDelete
  3. AnonymousFebruary 3, 2010 at 12:44 PM

    Good post. It helped me to create comma field. I put sql for above code sample:

    create table emp(id int,fname varchar(50),dept varchar(50))

    insert into emp values(1,'A','IT');
    insert into emp values(2,'B','IT');
    insert into emp values(3,'C','Sale');
    insert into emp values(4,'D','Sale')

    SELECT DISTINCT
    Dept, EmpNames = substring( ( SELECT ', ' + Fname
    FROM Emp e2
    WHERE e2.Dept = e1.Dept FOR XML path(''), elements
    ),2,500)
    FROM Emp e1

    Regards,
    Nilesh

    ReplyDelete
  4. sajanAugust 13, 2010 at 12:58 PM

    I Could not get the correct Result

    SELECT DISTINCT
    Membercode, EmpNames = substring( ( SELECT ', ' + Childname
    FROM Memberchildren e2
    WHERE e2.Childname = e1.Childname FOR XML path(''), elements
    ),1,500)
    FROM Memberchildren e1 where e1.Membercode='T005'


    T005 , NEWCHILDSSSS
    T005 , NEWTEST234RRRRR
    T005 , NEWTESTTTTTTTTTT
    T005 , TEEST
    T005 , TESTSSSSSSSS

    ReplyDelete
  5. AnonymousAugust 25, 2010 at 1:32 AM

    I could kiss you!

    ReplyDelete
  6. AnonymousAugust 25, 2010 at 1:33 AM

    I could kiss you! Thanks!

    ReplyDelete
  7. UnknownSeptember 9, 2010 at 11:24 PM

    Awesome! Exactly what I needed. Thanks for posting

    ReplyDelete
  8. UnknownSeptember 9, 2010 at 11:25 PM

    Awesome! Exactly what I needed. Thanks for posting.

    ReplyDelete
  9. AnonymousNovember 2, 2010 at 4:24 PM

    thanks very much for this post. It was very help full. I was facing the similar problem.

    Thanks again

    ReplyDelete
  10. Imamul KarimJanuary 27, 2011 at 8:57 PM

    Sql Query For Multiple Row To Single Column

    http://www.bdlab.byethost32.com/index.php/sql-server/97-sql-query-for-multiple-row-to-single-column

    ReplyDelete
  11. PeterFebruary 18, 2011 at 5:35 PM

    There is also a possibility to fix this on SQL2000 or older versions (which do not have the FOR XML PATH option).
    You can do this by joining the table as much times as you have multiple rows.

    An example:

    CREATE table #temp (col1 nvarchar(10), col2 nvarchar(1))

    INSERT INTO #temp values('ComputerA', 'C')
    INSERT INTO #temp values('ComputerA', 'D')
    INSERT INTO #temp values('ComputerA', 'E')
    INSERT INTO #temp values('ComputerB', 'C')
    INSERT INTO #temp values('ComputerC', 'C')
    INSERT INTO #temp values('ComputerC', 'D')


    SELECT x1.col1
    , REPLACE(ISNULL(MIN(x1.col2), ' ') + ', '
    + ISNULL(MIN(x2.col2), ' ') + ', '
    + ISNULL(MIN(x3.col2), ' ')
    , ', ', '') AS driveletters
    FROM #temp x1
    LEFT JOIN #temp x2
    ON x1.col1 = x2.col1
    AND x2.col2 > x1.col2
    LEFT JOIN #temp x3
    ON x1.col1 = x3.col1
    AND x3.col2 > x2.col2
    GROUP BY x1.col1

    DROP TABLE #temp

    In this example, if you have a maximum of 26 driveletters, you have to do 25 left joins.

    Hope this helps.

    Regards,

    Peter Elzinga

    ReplyDelete
  12. AnonymousOctober 18, 2011 at 6:19 PM

    Thanks Man! That's what I needed! ;o)

    RGDS,

    -- cs

    ReplyDelete
  13. AnonymousNovember 3, 2011 at 11:17 PM

    tried this and I'm having trouble in asp with how to form the response.write

    ReplyDelete
  14. HyderabadTabletsDecember 6, 2011 at 7:37 PM

    SELECT DISTINCT
    COMPANYID, Result = substring( ( SELECT ', ' + STATUS FROM CALLLOGS e2 WHERE e2.COMPANYID = e1.COMPANYID FOR XML path(''), elements),1,500)
    FROM CALLLOGS e1

    This gives me a error. Can some one help me in solving this problem.

    ReplyDelete
  15. UnknownDecember 7, 2011 at 2:03 PM

    @Above: What error you are getting ? , query seems fine

    ReplyDelete
  16. AnonymousDecember 13, 2011 at 7:14 PM

    hi

    ReplyDelete
  17. Charles P.January 12, 2012 at 9:45 PM

    This is a neat applet; however, I'd like to see this done in SQL Server 2000. Any ideas?

    ReplyDelete
  18. AnonymousJanuary 19, 2012 at 12:56 AM

    Fracking amazing, switched from a clunky coalesce function, to this, WOW!

    ReplyDelete
  19. AnonymousFebruary 13, 2012 at 8:55 PM

    Really thanks, appreciating

    ReplyDelete
  20. DEEPAK BHAKTAFebruary 22, 2012 at 11:11 AM

    Awesome ....

    ReplyDelete