pivot/unpivot under certain condition

elmasduro

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

Paulzip

select

  pt, netw, vst_prov_v as vst_prov, attn_prov_v as attn_prov, ref_prov_v as ref_prov

, blood_v as blood, pulse_v as pulse, height_v as height, weight_v as weight   

, case__v as "case", sight_v as sight, vst_prov_ndi, attn_prov_ndi, ref_prov_ndi    

from data2

   pivot (

     max(val) as v

   , max(nid) as ndi

     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

   )

)

order by pt

                                                                                                                                                 

PT	NETW	VST_PROV	ATTN_PROV	REF_PROV	BLOOD	PULSE	HEIGHT	WEIGHT	case	SIGHT	VST_PROV_NDI	ATTN_PROV_NDI	REF_PROV_NDI
123	PBN	alex	rob	will	453	1					4343	3456	123
345	KPT	herman	josepth				123				65	23
876	OUE							123

Paulzip

select

  pt, netw, vst_prov_v as vst_prov, attn_prov_v as attn_prov, ref_prov_v as ref_prov

, blood_v as blood, pulse_v as pulse, height_v as height, weight_v as weight   

, case__v as "case", sight_v as sight, vst_prov_ndi, attn_prov_ndi, ref_prov_ndi    

from data2

   pivot (

     max(val) as v

   , max(nid) as ndi

     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

   )

)

order by pt

                                                                                                                                                 

PT	NETW	VST_PROV	ATTN_PROV	REF_PROV	BLOOD	PULSE	HEIGHT	WEIGHT	case	SIGHT	VST_PROV_NDI	ATTN_PROV_NDI	REF_PROV_NDI
123	PBN	alex	rob	will	453	1					4343	3456	123
345	KPT	herman	josepth				123				65	23
876	OUE							123

Frank Kulash

Hi,

elmasduro wrote:hi all,i have an scenario where i want to unpivot data only for certain values. let me explain, consider the following datawith 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 valuesand 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_provi want my output to look like the followingpt netw ind vst_prov attn_prov ref_prov blood pulse height weight case sight vst_prov_ndi attn_prov_ndi ref_prov_ndi123 PBN test alex rob wil l 453 1 4343 3456 123876 OUE test2 123345 KPT test1 herman josepth 123 65 23as 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

It's hard to read the desired results.  Could you post them with less white-space between the columns, or with fewer columns?ndi

You can use SELECT ... PIVOT to get both val and ndi for all fieldnames.

Then, instead of SELECT *, explicitly specify just the columns you want, like this:

SELECT    pt, netw, ind,         v_v  AS vst_prov,         a_v  AS attn_prov,         r_v  AS ref_prov,         b_v  AS blood,         p_v  as pulse,         h_v  as height,         w_v  as weight,         c_v  as case_  -- CASE is not a good column name,         s_v  as sight,         v_n  AS vst_prov_ndi,         a_n  AS attn_prov_ndi,         r_n  AS ref_prov_ndiFROM      data1PIVOT     (     MAX (val)  AS v          ,     MAX (nid)  AS n          FOR  fieldname  IN ( 'vst_prov'  AS v                             , 'attn_prov' AS a                             , 'ref_prov'  AS r                             , 'blood'     AS b                             , 'pulse'     AS p                             , 'Height'    AS h                             , 'weight'    AS w                             , 'case'      AS c                             , 'sight'     AS s                             )          )ORDER BY  pt  -- or whatever you want;