Unpivot ALL Columns in a SQL Table

Question

I have a table with 30 columns and I want to easily unpivot ALL columns. I understand I can use this strategy:

SELECT col, value 
INTO New_Table
FROM
(SELECT * FROM Test_Data) p
UNPIVOT
(value FOR col IN (Column_Name1, Column_Name2... Column_Name30)) as unpvt

This is how my data comes in:

Column_Name1    Column_Name2    Column_Name3
Value11         Value21         Value31
Value12         Value22         Value32
Value13         Value23         Value33

This is how I want to store it in the new table:

New_Column1    New_cloumn2
Column_Name1   Value11
Column_Name1   Value12
Column_Name1   Value13
Column_Name2   Value21
...

But there must be an easier way than typing in the 30 column names.

Thanks in advance.

If you have a situation such that it makes sense that 30 columns should actually all be treated as a single column, it's a sign that your data model was seriously wrong in the first place. Why should there be an "easier way" to fix a problem of your own making? — Damien_The_Unbeliever, Feb 24 '14 at 19:27
@Damien_The_Unbeliever It is an upload of an Excel spreadsheet where each column header is a question code, and the responses are below — user3347843, Feb 24 '14 at 19:31
Yeah. SQL database tables are not the same thing as spreadsheets. Trying to treat them as the same thing leads to (bizarre) attempts to use them inappropriately. Major thing to learn at this point (probably) is that representation and storage are two different things. Just because you see a grid on screen doesn't mean the data needs to be stored in a grid. — Damien_The_Unbeliever, Feb 24 '14 at 19:37
@Damien_The_Unbeliever That last sentence, can you suggest a better way to store this data? — user3347843, Feb 24 '14 at 19:48
You have 30 columns that all (apparently) store the same "kind" of data (meaning that it's comparable, computable, etc). For each current row of data, you should instead have (up to) 30 rows of data, and the original data that you're referring to should already be in a single column. Transforming data for *display* should be a long distance away from how you are *storing* data. — Damien_The_Unbeliever, Feb 24 '14 at 19:50
@user3347843 - Is there a good, justifiable reason for having such a data model ? Please ask your dba. — Erran Morad, Feb 24 '14 at 19:55

Hart CO · Answer 1 · 2014-02-24T20:48:21.333

This seems like a broken data model, but you can avoid typing out lists of columns.

You can use this to generate a list of columns:

SELECT name
FROM sys.columns
WHERE objecT_id = OBJECT_ID('Test_Data')

And:

DECLARE @List VARCHAR(MAX) = STUFF((SELECT DISTINCT ',' +   QUOTENAME(name)                     
                                    FROM sys.columns
                                    WHERE objecT_id = OBJECT_ID('Test_Data')                                    
                                    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') 
                                    ,1,1,'')
PRINT @List

You can then use that list in a dynamic sql statement:

DECLARE @List VARCHAR(MAX) = STUFF((SELECT DISTINCT ',' +   QUOTENAME(name)                     
                                    FROM sys.columns
                                    WHERE objecT_id = OBJECT_ID('Test_Data')                                    
                                    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') 
                                    ,1,1,'')
        ,@sql VARCHAR(MAX)
SET  @sql  =  'SELECT  col,value  
               INTO New_Table
               FROM  (SELECT  *  FROM  Test_Data)  p
               UNPIVOT (value  FOR  col  IN  ('+@List+'))  as  unpvt
              '
EXEC  (@sql)

I suggest using PRINT (@sql) before using EXEC to ensure the dynamic query is what you were after.

Unpivot ALL Columns in a SQL Table

1 Answers1

Linked