Query which would return a specific form of a table in SQL Server 2008

Question

I have a table like this:

IliskiId    KulId   DptId   DosyaAdi
------------------------------------
   2        332      66     4-92
   3        332      66     9-50
   4        332      73     13-96
   5        332      73     15-19
   6        334      71     10-11
   7        334      72     35-14

I need a query which would return this particular example just like in this form:

KulId   DptId   DosyaAdi
---------------------------------
332      66     4-92, 9-50
332      73     13-96, 15-19
334      71     10-11
334      72     35-14

What is going on here is that I grouped the same DptId values in the same row and appended DosyaAdi values on top of the first one...there can be more than two cases as well, like 10 different DosyaAdi values for the same particular DptId.

I tried many ways but always got that "Subquery returned more than 1 value" error. I know that I need to use STUFF in order to group all DosyaAdi values in the same cell in that form though(separated with ","). I google'd a lot as well but couldn't find anything since this is a very specific problem.

Help really appreciated... I need a query for this result so bad.

I voted to re-open because the OP has attempted to use `STUFF()`. S/he should really include the query in the question, but knowing to use `FOR XML PATH` doesn't seem to be the issue here. — Gordon Linoff, Oct 11 '17 at 16:25

Gordon Linoff · Answer 1 · 2017-10-12T01:33:23.407

0

The subquery needs a correlation clause:

select t.*,
       stuff( (select ', ' + DosyaAdi
               from t t2
               where t2.DptId = t2.DptId and t2.DosyaAdi = t.DosyaAdi
               for xml path ('')
              ), 1, 2, ''
            ) as DosyaAdi
from (select distinct DptId, DosyaAdi
      from t
     ) t;

You also want to be sure that you get distinct rows, hence the select distinct subquery.

edited Oct 12 '17 at 01:33

answered Oct 11 '17 at 16:27

Gordon Linoff

1,242,037
58
646
786

I knew my STUFF code was right by looking at yours but like you said it isn't the main problem. However, I'm getting "Invalid column name 't2'" error. I only changed t's with my table name that are after the from's. t.t2.DptId is the part where it's raised. – odemmah Oct 11 '17 at 16:52

score 0 · Accepted Answer · answered Oct 12 '17 at 06:38

According, the output you are looking some thing like below which could help you:

SELECT DISTINCT
       T.KulId,
       T.DptId,
       STUFF(
            (
                SELECT ','+DosyaAdi
                FROM <table_name>
                WHERE KulId = T.KulId
                      AND DptId = T.DptId FOR XML PATH('')
            ), 1, 1, '') DosyaAdi
FROM <table_name> T;

Output :

KulId   DptId   DosyaAdi
---------------------------------
332      66     4-92, 9-50
332      73     13-96, 15-19
334      71     10-11
334      72     35-14

Query which would return a specific form of a table in SQL Server 2008

2 Answers2