pivoting data

Devx

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

Frank Kulash

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

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.

Frank Kulash

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

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.

Frank Kulash

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.