SQL Server Dynamic Pivot Column Names

Question

For the necessity of my application, I must return the column names of a query as the very first row. Now I must PIVOT this result in order to UNION it with my result set, but the difficult part is: it must be dynamic, so if I ever add new columns to this table, the SELECT will bring all the names pivoted.

The following SELECT brings me the Column names:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Codes'
ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION

And my result set is:

COLUMN_NAME
Id
CodeName
Country
StartDate
EndDate

What I expect is:

Id    CodeName    Country    StartDate    EndDate (... whatever other columns I might have)

Is there any easy way to do that without hardcoding the column names?

Thank you in advance!

http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query#10404455 you can use `stuff` part from this — Bharadwaj, Dec 22 '14 at 12:31
The expected result should have a separate column name for each 'COLUMN_NAME'. What is the preferred naming scheme? — Giorgos Betsos, Dec 22 '14 at 13:05

Sarath Subramanian · Accepted Answer · 2014-12-22T12:45:25.327

7

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + COLUMN_NAME + ']', 
               '[' + COLUMN_NAME + ']')
               FROM    (SELECT DISTINCT COLUMN_NAME,INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION O 
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = 'CODES') PV  
               ORDER BY O


DECLARE @query NVARCHAR(MAX)
SET @query = '           
              SELECT TOP 0 * FROM 
             (
                 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_NAME = ''CODES''
             ) x
             PIVOT 
             (
                 MIN(COLUMN_NAME)
                 FOR [COLUMN_NAME] IN (' + @cols + ')
            ) p    

            '     
EXEC SP_EXECUTESQL @query

edited Dec 22 '14 at 12:45

answered Dec 22 '14 at 12:34

Sarath Subramanian

20,027
11
82
86

Cool no need of promises here – Pரதீப் Dec 22 '14 at 13:13
yep it is working now. First time you were missing commas between columns – Pரதீப் Dec 22 '14 at 13:16
I think this should be what the OP requested. Good answer. – Giorgos Betsos Dec 22 '14 at 13:19
Thank you. Fell free to ask for any clarifications dude :) @user3472897 – Sarath Subramanian Jan 05 '15 at 12:14

score 3 · Answer 2 · answered Mar 07 '19 at 14:40

Starting with SQL Server 2017, there's a function for this: STRING_AGG. I used the QUOTENAME function as well here, to make adding the [ ] brackets easier.

DECLARE @ColumnNames NVARCHAR(MAX);
SELECT @ColumnNames = STRING_AGG(QUOTENAME(COLUMN_NAME), ',')
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME='Codes';

score 0 · Answer 3 · answered Dec 22 '14 at 12:29

0

Simple way would be by declaring a variable and assigning the columns with comma separted. Try this.

DECLARE @col_list VARCHAR(max)=''

SELECT @col_list += '['+ COLUMN_NAME + '],'
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'Codes'
ORDER  BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION

SELECT @col_list = LEFT(@col_list, Len(@col_list) - 1)

SELECT @col_list

answered Dec 22 '14 at 12:29

Pரதீப்

91,748
19
131
172

I think the OP wants a result set where each column name is contained in a *separate column*. – Giorgos Betsos Dec 22 '14 at 13:13
@GiorgosBetsos i Didn't get you OP wants to get the column list that can be used in pivot to generate columns dynamically. Run the above query in your DB and check – Pரதீப் Dec 22 '14 at 13:30
OK I had a different interpretation of the OP. As a side note I did not downvote your answeer. – Giorgos Betsos Dec 22 '14 at 13:42
@GiorgosBetsos - No thats cool. Just want to clarify with you – Pரதீப் Dec 22 '14 at 13:46

SQL Server Dynamic Pivot Column Names

3 Answers3

Linked