How to get a comma seperated value for the following DB table scenario

Question

How to get the following output from a table in MSSQL 2005 ?

Input
-----

main_idn notice_id       group_name    employer_name
    1         20        State Client      Unknown
    2         20        Canada Corp       Unknown
    3         20        Unknown           Pacific Bell
    4         30        State Client      Unknown 
    5         30        Reality Corp      Unknown
    6         30        Unknown           Bell Labs
    7         30        Unknown           AT&T

Output 
------

notice_idn  group_name                                    employer_name
20          State Client,Canada Corp,Unknown              Unknown , Unknown , Pacific Labs
30          State Client,Reality Corp.Unknown,Unknown     Unknown , Unknown , Bell Labs ,  AT&T

In something like MYSQL I can write a simple query like :

from table get notice_idn , group_concat(group_name) as group_name , group_concat(employer_name) as emp_name group by notice_idn

Search StackOverflow. There are plenty of answers to similar questions. — Saif Khan, Mar 19 '12 at 06:53
I have seen many such 'duplicates' but I am not able to correlate as much , because the join construct is confusing to me . Can anyone tell me a solution ? — Nishant, Mar 19 '12 at 07:21

score 1 · Accepted Answer · edited May 23 '17 at 12:20

The solution is already given here which I like, however would you please try with below solution though both are same kind of solution, thanks for your time.

SELECT outerT.notice_idn,

       STUFF(ISNULL((SELECT ', ' + innerT.group_name
                FROM TargetTableName innerT
               WHERE innerT.notice_id = outerT.notice_id
            GROUP BY innerT.notice_id, innerT.group_name
             FOR XML PATH (''), TYPE).value('.','VARCHAR(MAX)'), ''), 1, 1, '') group_name,

       STUFF(ISNULL((SELECT ', ' + innerT.employer_name
                FROM TargetTableName innerT
               WHERE innerT.notice_id = outerT.notice_id
            GROUP BY innerT.notice_id, innerT.employer_name
             FOR XML PATH (''), TYPE).value('.','VARCHAR(MAX)'), ''), 1, 1, '') employer_name             

FROM TargetTableName outerT
GROUP BY outerT.notice_id

Awesome thank you very much . I am new to DB queries . I need to learn the idea used in this solution - but perfect . — Nishant, Mar 19 '12 at 09:36

How to get a comma seperated value for the following DB table scenario

1 Answers1