Unpivot all values into a column

Question

I have a really wide table. All I want to do is put each value for the row into a single column. I also cannot hard code the column names as ill perform this operation on a number of different tables. So, it has to be dynamic. Lastly, please assume that there is only ever one row in the table.

Is there a simple way to UNPIVOT every value in a table?

Current

Col_A   Col_B   Col_C   Col_D
  a        b      c       d

Desired Result

Col_Index   Col_Value
    1           a
    2           b
    3           c
    4           d

Traditional examples of unpivot are not helpful as i need a simple, and dynamic, way of unpivoting every row/column. Unpivot is applicable to one or several, not all.

Duplicate of http://stackoverflow.com/questions/18026236/sql-server-columns-to-rows — iamdave, Aug 03 '16 at 15:29
Please refer [this](https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396) — techspider, Aug 03 '16 at 15:29
neither of these are helpful. I'm not trying to unpivot one value. I'm trying to unpivot every value. — William III, Aug 03 '16 at 15:31
when you unpivot, you have to convert all values to the same type.. is this something that will even be useful for you? — JamieD77, Aug 03 '16 at 15:37
i think i just hit that issue. Yeah, its fine, i can just make everything a string — William III, Aug 03 '16 at 15:46
Does this answer your question? [SQL Server : Columns to Rows](https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows) — MikeTeeVee, Dec 10 '19 at 00:24

JamieD77 · Accepted Answer · 2016-08-03T16:12:09.033

This should get the ordinal position as Col_Index and the converted fields as Col_Value dynamically

DECLARE @sql NVARCHAR(MAX),
        @columns NVARCHAR(Max),
        @ordinals NVARCHAR(Max),
        @table VARCHAR(200) = 'table'



SELECT @columns = STUFF ((SELECT    ',CAST(' + QUOTENAME(column_name) + ' AS VARCHAR(MAX)) AS ' + QUOTENAME(ORDINAL_POSITION)
                          FROM      information_schema.columns 
                          WHERE     table_name=@table
                          ORDER BY ORDINAL_POSITION FOR XML PATH('')), 1, 1, ''),
        @ordinals = STUFF ((SELECT  ',' + QUOTENAME(ORDINAL_POSITION)
                          FROM      information_schema.columns 
                          WHERE     table_name=@table
                          ORDER BY ORDINAL_POSITION FOR XML PATH('')), 1, 1, '')

SET @sql = N'
    SELECT  Col_Index,
            Col_Value
    FROM    ( SELECT ' +  @columns + ' FROM ' + @table + ') t
    UNPIVOT (
        Col_Value
        FOR Col_Index in (' + @ordinals + ')
    ) up
'

if you have multiple schemas, you will need to define that along with the table name, and prepend that to the table name in your select query

you are an SQL God and I worship you – William III Aug 03 '16 at 17:25 — William III, Aug 03 '16 at 17:25

score 0 · Answer 2 · answered Aug 03 '16 at 15:51

0

select a1.*,ROW_NUMBER() over (order by (select null)) as col_index from #t t
cross apply
(
values(col_a),
      (col_b),
      (col_c),
      (col_d)
)a1(val)

answered Aug 03 '16 at 15:51

TheGameiswar

27,855
8
56
94

score 0 · Answer 3 · answered Dec 10 '19 at 00:17

CREATE Table
ColTable
(
    Col_A VARCHAR(256),
    Col_B VARCHAR(256),
    Col_C VARCHAR(256),
    Col_D VARCHAR(256),
)

INSERT INTO ColTable (Col_A,Col_B,Col_C,Col_D)
Values ('a','b','c','d')

SELECT * FROM ColTable


SELECT
    Ordinal AS Col_Index
    ,ColInfo AS Col_Value
FROM
(
    SELECT 
        Col_A
        ,Col_B
        ,Col_C
        ,Col_D
        ,1 AS [OrdinalA]
        ,2 AS [OrdinalB]
        ,3 AS [OrdinalC]
        ,4 AS [OrdinalD]
    FROM 
        ColTable
    ) AS d
    UNPIVOT
    (
        ColInfo FOR ColInfos IN (Col_A, Col_B, Col_C, Col_D)
    ) AS l
    UNPIVOT
    (
        Ordinal FOR Ordinals IN (OrdinalA, OrdinalB, OrdinalC, OrdinalD)
    ) AS o
WHERE
    RIGHT(ColInfos,1) = RIGHT(Ordinals,1)


DROP TABLE ColTable

Unpivot all values into a column

3 Answers3