question

cmcstay avatar image
cmcstay asked

My aim is to use the rows of an already existing table as the columns of a new table

I have a query in existence already called #columns whereby it outputs a list of the distinct column headings over a number of specific existing tables: SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '%201703%IMP' I now want to create a new table which will have this list as it's column headings. I have attempted this problem but with no success. My attempted query looks something like this: SELECT (SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '%201703%IMP') FROM (SELECT OBJECT_NAME(ST.object_id) AS POLICY_TYPE FROM sys.dm_db_partition_stats ST INNER JOIN sys.objects AS O ON O.object_id = ST.object_id WHERE (index_id < 2) AND O.type = 'U' AND OBJECT_NAME(ST.object_id) LIKE '%201703%IMP' GROUP BY ST.object_id) ORDER BY POLICY_TYPE ASC; The second part of this code addresses the existing tables to be considered under the column headings. I'm not sure if I'm even on the right track but any advice would be much appreciated, Thanks.
sql-serveraggregates
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This sounds like the dynamic pivot in this previous question/answer - https://ask.sqlservercentral.com/questions/113981/i-want-to-pivot-table-dynamically.html The 'id' column in the sample query would map to COLUMN_NAME in your example
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.