Group in SQL Server 2012

Question

I have the following SQL query output

I would like to have the output in the following format

+--+------+
|ID|RoleID|
+--+------+
|1 |1, 4  |
+--+------+
|3 |1     |
+--+------+
|5 |1     |
+--+------+
|6 |6     |
+--+------+

So on.

How to I achieve this?

Answer 1

Please refer Martin Smith's comment for the solution.

For other's reference I have pasted the solution

SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names
FROM information_schema.columns AS extern
CROSS APPLY
(
    SELECT column_name + ','
    FROM information_schema.columns AS intern
    WHERE extern.table_name = intern.table_name
    FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY table_name, column_names;