group records bases on one column name in data set and display all the values corresponding to the column for which records are grouped in SQL

Question

For example, if I have a data-set as below

ID ITEM
1   A
2   B
3   C
1   B
1   C
2   A

then need a sql query which gives output as below:

ID  ITEM
1    A, B, C
2    B, A
3    C

That is how to group based on ID and display all the values pertaining to another column attributes in SQL.

This is known as a [tag:pivot] operation, and has established solutions for all systems — Clockwork-Muse, Sep 13 '16 at 06:02

Jibin Balachandran · Answer 1 · 2016-09-13T06:22:40.663

0

Replace the table names with your table name and use the below query:

Select src.ID,
       src.Items [Items]
    From
        (
            Select distinct T2.ID, 
                (
                    STUFF((Select T1.Item + ',' AS [text()]
                    From [Yourtable-Name] T1
                    Where T1.ID = T2.ID
                    ORDER BY T1.ID
                    For XML PATH ('')),1,1,'')
                ) [Items]
            From [YourTableName] T2
        ) [src]

edited Sep 13 '16 at 06:22

answered Sep 13 '16 at 06:12

Jibin Balachandran

3,271
1
21
38

Hi Jibin,Thanks for your response. – user1829708 Sep 14 '16 at 09:52
@user1829708 You can mark it as useful if it worked for you – Jibin Balachandran Sep 16 '16 at 05:08
This was useful but take time. i am looking for optimized query. – user1829708 Sep 16 '16 at 06:35

group records bases on one column name in data set and display all the values corresponding to the column for which records are grouped in SQL

1 Answers1