SQL Server from X columns make 1 columns

I have example table like this:

Column1    Column2     Column3        Column4          Column5
---------------------------------------------------------------
Dog         456         Long           Short            Small
Car         454         Blue           NULL             NULL
Fruit       466         Apple          Pear             NULL

And I expect table like this when XY columns will be put into 1 column

Column1    Column2     Column3
------------------------------
Dog         456         Long
Dog         456         Short
Dog         456         Small
Car         454         Blue
Fruit       466         Apple
Fruit       466         Pear

Thank you for your opinions when tables have over thousand rows.

Even I can do this in excel and then back import data to SQL Server

4 Answers

  1. Paul- Reply

    2019-11-14

    You can use unpivot as below:

    Select * from #unpivotdata
    unpivot( cols for col in([column3],[column4],[column5])) u
    

    Output as below:

    +---------+---------+-------+
    | Column1 | column2 | cols  |
    +---------+---------+-------+
    | Dog     |     456 | Long  |
    | Dog     |     456 | Short |
    | Dog     |     456 | Small |
    | Car     |     454 | Blue  |
    | Fruit   |     466 | Apple |
    | Fruit   |     466 | Pear  |
    +---------+---------+-------+
  2. Patrick- Reply

    2019-11-14

    SELECT Column1, Column2, Column3 FROM table WHERE Column3 IS NOT NULL
    UNION ALL
    SELECT Column1, Column2, Column4 FROM table WHERE Column4 IS NOT NULL
    UNION ALL
    SELECT Column1, Column2, Column5 FROM table WHERE Column5 IS NOT NULL
    
  3. Peter- Reply

    2019-11-14

    Using UNION ALL:

    SELECT col1, col2, col3
    FROM tab
    UNION ALL
    SELECT col1, col2, col4
    FROM tab
    WHERE col4 IS NOT NULL
    SELECT col1, col2, col5
    FROM tab
    WHERE col5 IS NOT NULL;
    
  4. Philip- Reply

    2019-11-14

    UnPivot would be more performant, but if the number of columns is unknowm.

    You many notice that only the "Key" columns are identified, so the width is dynamic.

    Example

    Declare @YourTable Table ([Column1] varchar(50),[Column2] varchar(50),[Column3] varchar(50),[Column4] varchar(50),[Column5] varchar(50))
    Insert Into @YourTable Values 
     ('Dog',456,'Long','Short','Small')
    ,('Car',454,'Blue',NULL,NULL)
    ,('Fruit',466,'Apple','Pear',NULL)
    
    Select A.[Column1]
          ,A.[Column2]
          ,[Column3] = C.Value
     From @YourTable A
     Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
     Cross Apply (
                    Select Field = a.value('local-name(.)','varchar(100)')
                          ,Value = a.value('.','varchar(max)') 
                     From  B.XMLData.nodes('/row')  as C1(n)
                     Cross Apply C1.n.nodes('./@*') as C2(a)
                     Where a.value('local-name(.)','varchar(100)') not in ('Column1','Column2')
                 ) C
    

    Returns

    enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>