CODEWITHSUNDEEP
sqldb2
Aish
Aish

Reputation: 185

How to split columns into rows in DB2?

My sample query returns the below data. I want to split the column data to rows.

I do not want to use UNION ALL as my query is very complex. Is there any other way to accomplish this? I am using DB2.

Current:
SKU_NBR   STR_NBR   ADDL_ORD_QTY    TRGT_OH_QTY   BUMP_INCR_QTY
301701     1902        60                40           20
346989     1902        60                             20

Expected:            
SKU_NBR     STR_NBR    Demand    
301701      1902        60   
301701      1902        40   
301701      1902        20   
346989      1902        60   
346989      1902       null  
346989      1902        20

Upvotes: 1

Views: 2632

Answers (2)

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 4005

In my eyes it could be useful to see the source of the value as well - if not you can skip the column "type". Here is a possible solution using tp as table name:

select sku_nbr,str_nbr, type, demand
  from tp , 
       lateral(values ('ADDL_ORD_QTY', tp.ADDL_ORD_QTY), 
                      ('TRGT_OH_QTY', tp.TRGT_OH_QTY),
                      ('BUMP_INCR_QTY', tp.BUMP_INCR_QTY)) 
        as q(type, demand);

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use a cross join:

with t as (
      <your query here>
     )
select t.SKU_NBR, t.STR_NBR,
       (case when n.n = 1 then ADDL_ORD_QTY
             when n.n = 2 then TRGT_OH_QTY
             when n.n = 3 then BUMP_INCR_QTY
        end) as demand
from t cross join
     (select 1 as n from sysibm.sysdummy1 union all
      select 2 from sysibm.sysdummy1 union all
      select 3 from sysibm.sysdummy1
     ) n;

Upvotes: 0

Related Questions