Search code examples
sqlsql-servert-sqlsqlxml

How to copy values Table with any columns to other tables with one columns


I want to copy columns from one table to other tables into one column.

My main table - TBL_Sku2:

 id       sku1                     sku2                sku3                 sku4            sku5       
 ---     -----------       -----------------    -------------------  -----------------     ---------
 1   1GBDDR3-1066-21       2GBDDR3-1066-21        4GBDDR3-1066-414          Null               Null
 2   512MBDDR2-533-1038           null              null                     null            null
 3   1GBDDR2-533-1068        512MBDDR2-533-1033       Null                 Null                    Null

Output should be like:

Copy of TBL_SKU_F1 :

      Id                      sku                          
    -----------       ----------------- 
       1                1GBDDR3-1066
       2                2GBDDR3-1066
       3                4GBDDR3-1066
       4                512MBDDR2-533
       5                1GBDDR2-533
       6                512MBDDR2-533

My attempts so far:

select 
    [SKU1], [SKU2], [SKU3],
    [SKU4], [SKU5], [SKU6]
from
    [dbo].[TBL_Sku2]
unpivot 
    (Sku for col_name in ([SKU1], [SKU2], [SKU3], [SKU4], [SKU5], [SKU6]));

Query attempt #2:

select 
    [SKU1], [SKU2], [SKU3], [SKU4], [SKU5], [SKU6]
from
    [dbo].[TBL_Sku2]
cross apply
    (values('SKU1', [SKU1]), ('SKU2', [SKU2]), ('SKU3', SKU3),
           ('SKU4', SKU4), ('SKU5', SKU5), ('SKU6', SKU6)) c(col, value)
where 
    value is not null

I can not to insert or convert these columns to one column...


Solution

  • select row_number() over( order by (select null)) as id,
    b.* from #temp t
    cross apply
    (
    values(sku1),
          (sku2),
          (sku3),
          (sku4),
          (sku5)
    ) b(final)
    where final is not null