CODEWITHSUNDEEP
sqlsql-server
kbs
kbs

Reputation: 21

How to SELECT DISTINCT with an ORDER BY and FOR XML in SQL Server

I need to find the different question in order they were asked by different ID's. If there is same question asked multiple times , I just need it once.I am using SQL server . Here is my example -

ID         Orderid               Question
1           1                    aaa
1           2                    bbb
1           2                    bbb
1           3                    ccc
1           3                    ccc
1           4                    ddd
1           5                    eee
2           1                    klm
2           2                    pqr
2           3                    abc
2           3                    abc
2           4                    ttt
2           5                    fff

I want 2 rows , with only distinct questions per ID and the order of question should be order by orderid

ID         Question
1          aaa,bbb,ccc,ddd,eee
2          klm,pqr,abc,ttt,fff

Upvotes: 1

Views: 347

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You seem to understand the functionality you need to use. You just need to put it together:

select i.id,
       stuff((select ',' + question
              from t t2
              where t2.id = t.id
              group by question
              order by min(orderid)
              for xml path ('')
             ), 1, 1, ''
            ) as question
from (select distinct id from t) i;

Upvotes: 3

Related Questions