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

Question

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...

i have edited the question,please look ,if it is correct..Also i have removed this line `if column Id could not , no need.` ,since i assumed you don't need nulls and this can be inferred from output — TheGameiswar, Jan 17 '17 at 14:20
The reason you are struggling here is because you have denormalized data structures. I would urge you to fix the normalization problem as part of this exercise. Then in the future querying this data becomes painless. — Sean Lange, Jan 17 '17 at 14:24

score 3 · Accepted Answer · answered Jan 17 '17 at 14:46

3

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

answered Jan 17 '17 at 14:46

TheGameiswar

27,855
8
56
94

canu help me this link:(http://dba.stackexchange.com/questions/161199/how-to-split-value-of-column-to-the-one-column-in-sql-server?noredirect=1#comment310176_161199) and answer here(http://stackoverflow.com/questions/41661155/how-to-split-value-of-column-to-dynamic-column-in-sql-server) – RedArmy Jan 17 '17 at 18:00

Edward · Answer 2 · 2017-01-17T14:53:00.607

1

You were very close with attempt 1:

     SELECT row_number() over (order by (select null)) as id, u.sku
     FROM [dbo].[TBL_Sku2]
     UNPIVOT (
        Sku 
        FOR SkuName IN (
            [SKU1]
          ,[SKU2]
          ,[SKU3]
          ,[SKU4]
          ,[SKU5]
          ,[SKU6]
)) AS  u;

Now you can normalize!

edited Jan 17 '17 at 14:53

answered Jan 17 '17 at 14:41

Edward

8,028
2
36
43

Thanks @Edward , attempting by xml+sql :) – RedArmy Jan 17 '17 at 14:53
1

I missed the row number - added from TheGameiswar 's answer – Edward Jan 17 '17 at 14:53
canu help me this link:(http://dba.stackexchange.com/questions/161199/how-to-split-value-of-column-to-the-one-column-in-sql-server?noredirect=1#comment310176_161199) and answer here(http://stackoverflow.com/questions/41661155/how-to-split-value-of-column-to-dynamic-column-in-sql-server) – RedArmy Jan 17 '17 at 18:00

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

2 Answers2