On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,534 Users
  • 2,269,755 Discussions
  • 7,916,775 Comments

Discussions

pivot/unpivot under certain condition

elmasduro
elmasduro Member Posts: 424
edited Aug 11, 2020 6:01PM in SQL & PL/SQL

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

Tagged:
elmasduro

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Aug 7, 2020 4:39PM Answer ✓

    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

                                                                                                                                                     

    PTNETWVST_PROVATTN_PROVREF_PROVBLOODPULSEHEIGHTWEIGHTcaseSIGHTVST_PROV_NDIATTN_PROV_NDIREF_PROV_NDI
    123
    PBNalexrobwill4531
    4343
    3456
    123
    345
    KPThermanjosepth123
    65
    23
    876
    OUE123

Answers

  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Aug 7, 2020 4:39PM Answer ✓

    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

                                                                                                                                                     

    PTNETWVST_PROVATTN_PROVREF_PROVBLOODPULSEHEIGHTWEIGHTcaseSIGHTVST_PROV_NDIATTN_PROV_NDIREF_PROV_NDI
    123
    PBNalexrobwill4531
    4343
    3456
    123
    345
    KPThermanjosepth123
    65
    23
    876
    OUE123
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,001 Red Diamond
    edited Aug 7, 2020 5:16PM

    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;
    elmasduroelmasduro