x

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.

more ▼

asked Jul 13 at 01:29 PM in Default

avatar image

cmcstay
1

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

Jul 18 at 10:11 PM KenJ
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x440
x75

asked: Jul 13 at 01:29 PM

Seen: 29 times

Last Updated: Jul 19 at 09:09 AM

Copyright 2017 Redgate Software. Privacy Policy