How to separate 1 record to multiple records?

I want to use SQL to separate 1 record to multiple records

Original Table:

+---------+------+------+------+
| orderid | qty1 | qty2 | qty3 |
+---------+------+------+------+
| 1234    | 5    | 6    | 7    |
+---------+------+------+------+

I want to split to :

+---------+------+------+------+
| orderid | qty1 | qty2 | qty3 |
+---------+------+------+------+
| 1234    | 5    |      |      |
+---------+------+------+------+
| 1234    |      | 6    |      |
+---------+------+------+------+
| 1234    |      |      | 7    |
+---------+------+------+------+

How to do this, please help me,thanks!

2 answers

  • answered 2018-02-13 03:29 Prabhath Amaradasa

    You can try something like this

    SELECT orderid , qty1 ,NULL, NULL FROM  Order_Tbl  
    UNION ALL
    SELECT orderid , NULL, qty2, NULL  FROM  Order_Tbl  
    UNION ALL
    SELECT orderid , NULL ,NULL, qty3 FROM  Order_Tbl 
    

  • answered 2018-02-13 03:36 Gordon Linoff

    Some dialects of SQL support explicit lateral joins. Otherwise, if you care about performance, you can use cross join. Something like this:

    select t.orderid,
           (case when n.n = 1 then qty1 end) as qty1,
           (case when n.n = 2 then qty2 end) as qty2,
           (case when n.n = 3 then qty3 end) as qty3
    from t cross join
         (select 1 as n union all select 2 as n union all select 3 as n) n;
    

    More typically when splitting data like this, you would want all the values in a single column, with perhaps another column specifying the original column:

    select t.orderid, n.n,
           (case when n.n = 1 then qty1
                 when n.n = 2 then qty2
                 when n.n = 3 then qty3
            end) as qty
    from t cross join
         (select 1 as n union all select 2 as n union all select 3 as n) n;