CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005sql-server-2008-r2
Gopal Krishna Ranjan
Gopal Krishna Ranjan

Reputation: 878

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

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.

Upvotes: 3

Views: 5947

Answers (1)

John Woo
John Woo

Reputation: 263703

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

Upvotes: 3

Related Questions