unkn0wn unkn0wn - 1 year ago 52
SQL Question

Split and Merge values of columns of a table

I have my output data in following given format,

select
trans_store_id as TRANS_WAREHOUSE_NAME,
demand_store_id as DEMAND_WAREHOUSE_NAME,
item_id as ITEM_NAME,
NVL(transfer_qty,0) as TRANSFER_QTY
from ORDER_DTL
WHERE gnum_isvalid=1




col1 col2 col3 col4
A B item1 500
A C item2 200
C B item1 300


I wanted to get data in following format,

col1 col2 col3 col4
A item1 500 0
B item1 0 500
A item2 200 0
C item2 0 200
C item1 300 0
B item1 0 300


Kindly excuse lack of queries because i have no idea where to start. Thanks.

Answer Source

Just use union all:

select col1, col3 as col2, col4 as col3, 0 as col4
from t
union all
select col2, col3, 0, col4
from t;

You have edited the question. You can use a CTE:

with t as (
      select trans_store_id, demand_store_id, 
             item_id, NVL(transfer_qty, 0) as transfer_qty 
      from ORDER_DTL
      where gnum_isvalid = 1
     )
select trans_store_id, item_id, transfer_qty, 0 as other_qty
from t
union all
select demand_store_id, item_id, 0, transfer_qty as other_qty
from t;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download