August 6, 2013 at 1:20 am
The current result when take from INFORMATION_SCHEMA.COLUMNS
TABLE COLUMN
Table1 Column1
Table1 Column2
Table1 Column3
Table2 Column1
Table2 Column2
Table2 Column3
Expected result
TABLE COLUMNS
Table1 Column1,Column2,Column3
Table2 Column1,Column2,Column3
I have tried 2 methods
Method 1
SELECT c1.Table_name,
STUFF((Select ',' + c2.COLUMN_NAME AS [text()]
from INFORMATION_SCHEMA.COLUMNS c2
where c1.TABLE_NAME=c2.TABLE_NAME and c1.column_name=c2.column_name
--Order by c2.TABLE_NAME
for xml path ( '' )), 1, 1,'' ) as "Column_names"
from INFORMATION_SCHEMA.COLUMNS c1
group by Table_name
Which throws the error
Msg 8120, Level 16, State 1, Line 4
Column 'INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
But adding column_name to the group by clause does not give the desired result
Method 2
;WITH CTE (SRNO,Table_name,column_name)
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY Table_name ORDER BY Table_name,column_name) AS SRNO,Table_name,CAST(column_name AS VARCHAR(MAX)) FROM INFORMATION_SCHEMA.COLUMNS
)
,CTE1(SRNO,Table_name,column_name)
AS
(
SELECT * FROM CTE WHERE SRNO=1
UNION ALL
SELECT CTE.SRNO AS SRNO, CTE.Table_name,CAST (CTE1.column_name + ',' + CTE.column_name AS VARCHAR(MAX)) FROM CTE INNER JOIN CTE1
ON CTE.Table_name=CTE1.Table_name AND CTE.SRNO=CTE1.SRNO+1
)
SELECT Table_name, MAX(column_name) AS CSV FROM CTE1 GROUP BY CTE1.Table_name
option (maxrecursion 100)
But this takes a very long time.
Kindly help me get the desired result in the most optimum way
August 6, 2013 at 1:24 am
August 6, 2013 at 1:34 am
Try this:
SELECT
c1.Table_name
,STUFF(
(
SELECT ',' + c2.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c2
WHERE c1.TABLE_NAME = c2.TABLE_NAME
FOR XML PATH ( '' ), TYPE
).value('.', 'varchar(max)')
, 1, 1,'' ) AS "Column_names"
FROM INFORMATION_SCHEMA.COLUMNS c1
GROUP BY Table_name;
You made a mistake in the WHERE clause in the FOR XML statement.
By the way, ColdCoffee is right, you should learn how to post questions. It is hard to read what you are actually trying to say.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply