hi all,
i have an scenario where i want to unpivot data only for certain values. let me explain,
consider the following data
with data1 as
(
select 123 pt, 'PBN' Netw, 'test' ind, 'vst_prov' fieldname, 'alex' val, 4343 nid from dual union all
select 123 pt, 'PBN' Netw, 'test' ind, 'attn_prov' fieldname, 'rob' val, 3456 nid from dual union all
select 123 pt, 'PBN' Netw, 'test' ind, 'ref_prov' fieldname, 'will' val, 123 nid from dual union all
select 123 pt, 'PBN' Netw, 'test' ind, 'blood' fieldname, '453' val, null nid from dual union all
select 123 pt, 'PBN' Netw, 'test' ind, 'pulse' fieldname, '1' val,null nid from dual union all
select 345 pt, 'KPT' Netw, 'test1' ind, 'vst_prov' fieldname, 'herman' val, 65 nid from dual union all
select 345 pt, 'KPT' Netw, 'test1' ind, 'attn_prov' fieldname, 'josepth' val, 23 nid from dual union all
select 345 pt, 'KPT' Netw, 'test1' ind, 'Height' fieldname, '123' val, null nid from dual union all
select 876 pt, 'OUE' Netw, 'test2' ind, 'weight' fieldname, '123' val, null nid from dual union all
select 876 pt, 'OUE' Netw, 'test2' ind, 'case' fieldname, null val, null nid from dual union all
select 876 pt, 'OUE' Netw, 'test2' ind, 'sight' fieldname, null val, null nid from dual
)
,data2 as(
select pt, netw, ind, fieldname, val from data1
)
select *
FROM data2
PIVOT (
MAX(val) FOR fieldname IN (
'vst_prov' AS vst_prov
,'attn_prov' AS attn_prov
,'ref_prov' AS ref_prov
,'blood' AS blood
,'pulse' AS pulse
,'Height' AS Height
,'weight' AS weight
,'case' AS case
,'sight' AS sight
)
)
i am trying to pivot the data base on fieldname and grabbing their corresponding value from the val column. so i am transforming rows to column.
the problem that i am having is that i want to introduce the nid column in my resultset. including the nid column will mess up the pivot because of the different values
and nid is not a fieldname but an actual physical column. i am only interested in getting the value for ind when field name = vst_prov, attn_prov, or ref_prov
i want my output to look like the following
pt netw ind vst_prov attn_prov ref_prov blood pulse height weight case sight vst_prov_ndi attn_prov_ndi ref_prov_ndi
123 PBN test alex rob wil l 453 1 4343 3456 123
876 OUE test2 123
345 KPT test1 herman josepth 123 65 23
as you can see, only field name = vst_prov, attn_prov, or ref_prov will have a column with nid values. any other fieldname should not have a corresponding ndi column in the output.
pivot alone is not working.
can someone help me rewrite my query to produce the output above? i am using oracle 11g.
thanks in advance