Forum Stats

  • 3,838,564 Users
  • 2,262,383 Discussions
  • 7,900,687 Comments

Discussions

pivoting data

Devx
Devx Member Posts: 143 Bronze Badge

Hi all, consider the following data


WITH data1 as

(

 select 123 pid, 555 ad_id, 'pox' imm from dual union all

 select 222 pid, 666 ad_id, 'pox' imm from dual union all

 select 222 pid, 777 ad_id, 'pox' imm from dual union all

 select 333 pid, 888 ad_id, 'pox' imm from dual union all

 select 333 pid, 999 ad_id, 'pox' imm from dual 

)


I am looking into pivoting the data. each pid will have one or two rows in the table. I want to pivot the data is such a way that produce the following output:


pid imm dose1 dose2 ad_id1 ad_id2

=========================================================

123 pox 1 0 555

222 pox 1 1 666 777

333 pox 1 1 888 999


as mentioned above, each pid can have one or two rows in the table with different ad_id value. each row represent a dosage

i want to pivot the data and create additional columns. for example. pid=123 only has one row so this is one dosage and dose1 column will be 1 and dose2 will be 0 because there is no second row. for pid=222 , we have two rows so dose1 will be 1 and dose2 will be 1 too. also ad_id has two values and therefore we display ad_id1 as 666 abd ad_id2 as 777. same scenario happens for pid=333.

i am thinking pivot function can do the job which i am not too familiar.

i am using oracle 11g. can someone help me write a query to produce the above output.

thanks in advance

Tagged:

Best Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond
    Answer ✓

    Hi, @Devx

    I want to pivot the data is such a way that produce the following output:


    pid imm dose1 dose2 ad_id1 ad_id2

    =========================================================

    123 pox 1 0 555

    222 pox 1 1 666 777

    333 pox 1 1 888 999

    So, does1 will always be 1, and dose2 will always be 0 (when there is only one row for that pid/imm combination) or 1 (when there are two). Is that right? If so, you can do something like this:

    WITH  data_to_pivot  AS
    (
      SELECT pid, ad_id, imm
      ,	  ROW_NUMBER () OVER ( PARTITION BY pid
     			     ORDER BY   ad_id
      	  	    	    ) AS rn
      FROM  data1
    )
    SELECT  *
    FROM	 data_to_pivot
    PIVOT	 (  COUNT (*)	AS dose
    	 ,  MIN (ad_id)	AS ad_id
    	 FOR rn IN ( 1 AS "1"
    	    	   , 2 AS "2"
    		   )
    	 )
    ORDER BY pid, imm	-- or whatever you want
    ;
    

    This gets the results you requested, but the column names and order are not what you posted:

        PID IMM 1_DOSE 1_AD_ID 2_DOSE 2_AD_ID
    ------- --- ------- ------- ------- -------
        123 pox       1     555       0
        222 pox       1     666       1     777
        333 pox       1     888       1     999
    

    To get the columns in the right order, with the right aliases, explicitly list each column instead of saying SELECT * .

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,594 Red Diamond
    edited Jul 22, 2022 4:07PM Answer ✓

    Assuming there are no more that 2 rows per pid, imm combination:

    WITH data1 as
    (
     select 123 pid, 555 ad_id, 'pox' imm from dual union all
     select 222 pid, 666 ad_id, 'pox' imm from dual union all
     select 222 pid, 777 ad_id, 'pox' imm from dual union all
     select 333 pid, 888 ad_id, 'pox' imm from dual union all
     select 333 pid, 999 ad_id, 'pox' imm from dual 
    )
    select  pid,
            imm,
            1 dose1,
            count(*) - 1 dose2,
            min(ad_id) ad_ad1,
            case count(*)
              when 2 then max(ad_id)
            end ad_ad2
      from  data1
      group by pid,
               imm
      order by pid,
               imm
    /
    
           PID IMM      DOSE1      DOSE2     AD_AD1     AD_AD2
    ---------- --- ---------- ---------- ---------- ----------
           123 pox          1          0        555
           222 pox          1          1        666        777
           333 pox          1          1        888        999
    
    SQL>
    

    SY.

    Devx

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond
    Answer ✓

    Hi, @Devx

    I want to pivot the data is such a way that produce the following output:


    pid imm dose1 dose2 ad_id1 ad_id2

    =========================================================

    123 pox 1 0 555

    222 pox 1 1 666 777

    333 pox 1 1 888 999

    So, does1 will always be 1, and dose2 will always be 0 (when there is only one row for that pid/imm combination) or 1 (when there are two). Is that right? If so, you can do something like this:

    WITH  data_to_pivot  AS
    (
      SELECT pid, ad_id, imm
      ,	  ROW_NUMBER () OVER ( PARTITION BY pid
     			     ORDER BY   ad_id
      	  	    	    ) AS rn
      FROM  data1
    )
    SELECT  *
    FROM	 data_to_pivot
    PIVOT	 (  COUNT (*)	AS dose
    	 ,  MIN (ad_id)	AS ad_id
    	 FOR rn IN ( 1 AS "1"
    	    	   , 2 AS "2"
    		   )
    	 )
    ORDER BY pid, imm	-- or whatever you want
    ;
    

    This gets the results you requested, but the column names and order are not what you posted:

        PID IMM 1_DOSE 1_AD_ID 2_DOSE 2_AD_ID
    ------- --- ------- ------- ------- -------
        123 pox       1     555       0
        222 pox       1     666       1     777
        333 pox       1     888       1     999
    

    To get the columns in the right order, with the right aliases, explicitly list each column instead of saying SELECT * .

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,594 Red Diamond
    edited Jul 22, 2022 4:07PM Answer ✓

    Assuming there are no more that 2 rows per pid, imm combination:

    WITH data1 as
    (
     select 123 pid, 555 ad_id, 'pox' imm from dual union all
     select 222 pid, 666 ad_id, 'pox' imm from dual union all
     select 222 pid, 777 ad_id, 'pox' imm from dual union all
     select 333 pid, 888 ad_id, 'pox' imm from dual union all
     select 333 pid, 999 ad_id, 'pox' imm from dual 
    )
    select  pid,
            imm,
            1 dose1,
            count(*) - 1 dose2,
            min(ad_id) ad_ad1,
            case count(*)
              when 2 then max(ad_id)
            end ad_ad2
      from  data1
      group by pid,
               imm
      order by pid,
               imm
    /
    
           PID IMM      DOSE1      DOSE2     AD_AD1     AD_AD2
    ---------- --- ---------- ---------- ---------- ----------
           123 pox          1          0        555
           222 pox          1          1        666        777
           333 pox          1          1        888        999
    
    SQL>
    

    SY.

    Devx
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond

    Hi, @Devx

    Both of the solutions above make assumptions about how to handle ties, what to do when there are more than two rows for the same pid/imm combination, how to chose which value is ad_id_1 and which is ad_id_2, and so on. If they don't meet your exact specfications, theyprobably only need minor changes.