Comma Separated list of rows of a column with group by on other columns

Question

Below is the structure of table I have: -

Table T1

C1  C2  C3
---------- 
X   P   A
X   P   B
Y   Q   C
Y   Q   D

Desired output: -

C1   C2   C3
------------ 
X    P    A,B
Y    Q    C,D

Note: - I know i can do the same with For XML('') with group by on C1 and C2, but the main problem in my case is that the table T1 here must be a physical table object (either permanent or temp or table var or CTE) in DB. But in my case it's a derived table and when i am using the below query it's saying invalid object. In my case it's not good to replace the derived table with temp# tables or fixed tables or even with CTE or table variable because it will take a great effort.

SELECT 
b.C1, b.C2, Stuff((',' + a.C3 from t1 a where a.c1 = b.c1 for XML PATH('')),1,1,'') FROM
T1 b group by b.c1,b.c2

I did not have T1 as fixed table. Please consider it as derived table only.

I need the solution with existing derived table. Please help.

Below is the query with derived table: - Please consider this only as a demo query. It's not as simple as given below and a lot of calculations have done to get the derived tables and 4 levels of derived tables have been used.

SELECT C1, C2, Stuff((',' + a.C3 from A B where a.c1 = b.c1 for XML PATH('')),1,1,'')
FROM
(
SELECT C1, C2, C3  FROM T1 WHERE C1 IS NOT NULL--and a lot of calculation also
)A

Please mind that T1 is not just below one step, in my case T1 the actual physical table is 4 level downs by derived tables.

http://stackoverflow.com/questions/12668528/sql-server-group-by-clause-to-get-comma-separated-values — Bratch, Dec 24 '15 at 01:04

score 3 · Answer 1 · edited Jan 24 '13 at 06:25

3

If you can post the query the produces derived table, we can help you work it out, but as of the moment try substituting table1 with the derived query.

;WITH Table1
AS
(
    SELECT C1, C2, C3  FROM T1 WHERE C1 IS NOT NULL--and a lot of calculation also
)
SELECT
     C1,C2,
     STUFF(
         (SELECT ',' + C3
          FROM Table1
          WHERE C1 = a.C1 AND C2 = a.C2
          FOR XML PATH (''))
          , 1, 1, '')  AS NamesList
FROM Table1 AS a
GROUP BY C1,C2

SQLFiddle Demo

edited Jan 24 '13 at 06:25

Hamlet Hakobyan

32,965
6
52
68

answered Jan 24 '13 at 06:10

John Woo

258,903
69
498
492

1

Dear @JW. i have mentioned that i did not have any fixed table and Table1 here is a derived table and thus not accessible inside subquery. Please suggest me by considering this constraint. – Gopal Krishna Ranjan Jan 24 '13 at 06:13
@GKRanjan why do you think a derived table is not accessible? try posting the query producing the derived table. A `CTE` can help. – John Woo Jan 24 '13 at 06:14
hi @JW please recheck the original question. – Gopal Krishna Ranjan Jan 24 '13 at 06:22
Hi @HamletHakobyan thanks for the answer, but why i can not do the same with derived table. From optimization point of view - CTE hits the base tables as many times as it referenced in Select statement. Thus in above case it will referenced two times. Also is there any way to get the same with COALESCE fucntion? Please suggest me. Thanks A lot. – Gopal Krishna Ranjan Jan 24 '13 at 06:31

Comma Separated list of rows of a column with group by on other columns

1 Answers1

Linked