Use SELECT Result As Column Names

Question

Morning all, Longtime lurker, first time poster. I'm writing a stored procedure to take the results from one SELECT statement and use that output to build another SELECT statement. Trouble is I can not find a mechanism or code to accomplish this. Assigning the results to a variable doesn't seem to be an option because SQL doesn't support Arrays (that I know of).

+----+-------------+-------------+
| ID | ___$seqval  | Column_Name |
+----+-------------+-------------+
| 1  | 0x000000E10 | EnvType     |
| 2  | 0x000000E10 | DataType    |
| 3  | 0x000000E10 | DateMod     |
+----+-------------+-------------+

Trying to get to; SELECT Column_name(1), Column_name(2).. From tblServer_Data

There could be up to a total of 20 columns returned.

I don't really follow what you're trying to achieve here. Sample data, expected results and your attempts will greatly help us here. — Thom A, Jul 23 '19 at 15:49
A good place to start: https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/ — Tim Biegeleisen, Jul 23 '19 at 15:50
You probably want dynamic pivot. https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Juan Carlos Oropeza, Jul 23 '19 at 15:50
@Larnu I believe the OP wants to build the query `SELECT EnvType, DataType, DateMod FROM tblServer_Data`, using the table which is actually shown. This requires dynamic SQL, which is above my pay grade and quite scary to me. — Tim Biegeleisen, Jul 23 '19 at 15:51
I did wonder if that's what they're after, but if they *are* after dynamic SQL then samples and expected results are even more important. I don't mind dynamic SQL though. It's not scary at all once you "get used" to it; especially if you just treat it like normal SQL first, and then (safely) turn it into a dynamic statement. — Thom A, Jul 23 '19 at 15:54
Let me try to clarify, I need to use the data from the "column_name" column as columns in a new SELECT statement. I could do it in C# but I'd like to keep it on the SQL server. — Charles_H, Jul 23 '19 at 16:00
What would really help here is table definitions, sample data and desired output. It just isn't clear at all what you are trying to do. — Sean Lange, Jul 23 '19 at 16:06

score 0 · Answer 1 · answered Jul 23 '19 at 16:26

This will give a SELECT statement of the columns in a selected table.

SELECT 'SELECT ' + STUFF(
(SELECT ',' + c.column_name  from
(SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME = your_table) c
for xml path ('')),1,1,'') + ' FROM ' + your_table

score 0 · Accepted Answer · answered Jul 23 '19 at 16:29

SQL DEMO

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME([Column_Name]) 
                   FROM Table1 c
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)') 
                 ,1,1,'')

SELECT @cols as Columns;  -- just for debug

set @query = 'SELECT [ID], ' + @cols + ' from 
            (
                select 1 as ID
                    , [seqval]
                    , [Column_Name]
                from Table1
           ) x
            pivot 
            (
                 max([seqval])
                for [Column_Name] in (' + @cols + ')
            ) p '

SELECT @query as Query;  -- just for debug    

execute(@query);

OUTPUT

Then you can do SELECT over that output

Use SELECT Result As Column Names

2 Answers2