Oracle convert multiple rows to column using pivot

oracleoracle-11g-r2pivot

I have a table that contains the values

  +-------------+--------------+---------------------+-------------+-------------+--------------+-------+---------+-----------+---------------+--------------+
|     ID      |     TYPE     |     LINE_1_ADDR     | LINE_2_ADDR | LINE_3_ADDR |   CITY_NAM   | STATE | COUNTRY | EFFEC_DT  | ROW_UPDATE_DT | ADDR_ROLE_CD |
+-------------+--------------+---------------------+-------------+-------------+--------------+-------+---------+-----------+---------------+--------------+
| 10114753088 | SBS CUSTOMER | 2095 CAPP HARLIN RD | Apt #225    |             | SIMPSONVILLE | MN    | USA     | 27-Jun-04 | 20-Mar-15     | BILL         |
| 10114753088 | SBS CUSTOMER | 304 S MAGNOLIA ST   |             | P.NO:516    | Toronto      | ON    | CAN     | 27-Jun-04 | 21-Mar-15     | PHYSICAL     |
| 10114753088 | SBS CUSTOMER | 3013 Xenium ln      |             |             | LAKEVILLE    | MN    | USA     | 27-Jun-04 | 20-Mar-15     | BILL         |
| 10114753089 | SBS CUSTOMER | 123 hancock ST      |             | apt 214     | quincy       | MA    | USA     | 27-Jun-04 | 21-Mar-15     | PHYSICAL     |
+-------------+--------------+---------------------+-------------+-------------+--------------+-------+---------+-----------+---------------+--------------+

I would like to have the data represented as:

+-------------+--------------+---------------------+-----------+-----------+-------------------+-----------+-----------+----------------+-----------+-----------+--------------+---------+-----------+---------+---------+---------+-----------+-----------+-----------+------------+------------+------------+---------------+---------------+---------------+----------------+----------------+----------------+
|     ID      |     TYPE     |      ADDR_L1_1      | ADDR_L1_2 | ADDR_L1_3 |     ADDR_L2_1     | ADDR_L2_2 | ADDR_L2_3 |   ADDR_L3_1    | ADDR_L3_2 | ADDR_L3_3 |    City_1    | City_2  |  City_3   | State_1 | State_2 | State_3 | Country_1 | Country_2 | Country_3 | EFFEC_DT_1 | EFFEC_DT_2 | EFFEC_DT_3 | INACTIVE_DT_1 | INACTIVE_DT_2 | INACTIVE_DT_3 | ADDR_ROLE_CD_1 | ADDR_ROLE_CD_2 | ADDR_ROLE_CD_3 |
+-------------+--------------+---------------------+-----------+-----------+-------------------+-----------+-----------+----------------+-----------+-----------+--------------+---------+-----------+---------+---------+---------+-----------+-----------+-----------+------------+------------+------------+---------------+---------------+---------------+----------------+----------------+----------------+
| 10114753088 | SBS CUSTOMER | 2095 CAPP HARLIN RD | Apt #225  |           | 304 S MAGNOLIA ST |           | P.NO:516  | 3013 Xenium ln |           |           | SIMPSONVILLE | Toronto | LAKEVILLE | MN      | ON      | MN      | USA       | CAN       | USA       | 27-Jun-04  | 27-Jun-04  | 27-Jun-04  | 20-Mar-15     | 21-Mar-15     | 20-Mar-15     | BILL           | PHYSICAL       | BILL           |
| 10114753089 | SBS CUSTOMER | 123 hancock st      | Apt #214  |           |                   |           |           |                |           |           | quincy       |         |           | MA      |         |         | USA       |           |           | 27-Jun-04  |            |            | 21-Jun-04     |               |               | PHYSICAL       |                |                |
+-------------+--------------+---------------------+-----------+-----------+-------------------+-----------+-----------+----------------+-----------+-----------+--------------+---------+-----------+---------+---------+---------+-----------+-----------+-----------+------------+------------+------------+---------------+---------------+---------------+----------------+----------------+----------------+

I am trying to use pivot function but able to get exact logic how to get this. Can anyone provide some inputs.

Best Answer

You can have multiple aggregates within a PIVOT but you must alias them.

Doing this will add the alias of the aggregate as a SUFFIX for the resulting column name. (Your requirement is to have them as a PREFIX)

The FOR x in ( ) entries can also have a column name. These will be the PREFIX for the final column.

I've modified the requirement, but here is the result.

with data (ID,TYPE,LINE_1_ADDR,LINE_2_ADDR,LINE_3_ADDR
          ,CITY_NAM, STATE , COUNTRY , EFFEC_DT  , ROW_UPDATE_DT 
          ,ADDR_ROLE_CD )
as (
SELECT
  10114753088
 ,'SBS CUSTOMER'
 ,'2095 CAPP HARLIN RD'
 ,'Apt #225'
 ,''
 ,'SIMPSONVILLE'
 ,'MN'
 ,'USA'
 ,TO_DATE( '27-Jun-04','rr-mon-dd' )
 ,TO_DATE( '20-Mar-15','rr-mon-dd' )
 ,'BILL'
FROM dual
UNION ALL
SELECT 
  10114753088
 ,'SBS CUSTOMER'
 ,'304 S MAGNOLIA ST'
 ,''
 ,'P.NO:516'
 ,'Toronto'
 ,'ON'
 ,'CAN'
 ,TO_DATE( '27-Jun-04','rr-mon-dd' )
 ,TO_DATE( '21-Mar-15','rr-mon-dd' )
 ,'PHYSICAL'
FROM dual)
select *
from data
pivot (
   min(LINE_1_ADDR) as "LINE_1_ADDR"
  ,min(LINE_2_ADDR) AS "LINE_2_ADDR"
  ,min(LINE_3_ADDR) as "LINE_3_ADDR"
  ,min(CITY_NAM) as "CITY_NAM"
  ,min(STATE) as "STATE"
  ,min(COUNTRY) as "COUNTRY"
  ,min(EFFEC_DT) as "EFFEC_DT"
  ,min(ROW_UPDATE_DT) as "ROW_UPDATE_DT"
  for addr_role_cd in ( 'BILL' as "BILLING", 'PHYSICAL' as "PHYS" )
)

Related Solutions

How to Pivot Rows into Multiple Columns in Oracle

There are a few ways that you can perform this data transformation. You have access to the PIVOT function then that will be the easiest, but if not then you can use an aggregate function and a CASE.

Aggregate /Case version:

select personid,
  max(case when optionid = 'A' then 1 else 0 end) OptionA,
  max(case when optionid = 'B' then 1 else 0 end) OptionB,
  max(case when optionid = 'C' then 1 else 0 end) OptionC
from PersonOptions
group by personid
order by personid;

See SQL Fiddle with Demo

Static Pivot:

select *
from
(
  select personid, optionid
  from PersonOptions
) src
pivot
(
  count(optionid)
  for optionid in ('A' as OptionA, 'B' OptionB, 'C' OptionC)
) piv
order by personid

See SQL Fiddle with Demo

Dynamic Version:

The two versions above work great if you have a known number of values, but if your values are unknown, then you will want to implement dynamic sql and in Oracle you can use a procedure:

CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select personid ';

    begin
        for x in (select distinct OptionID from PersonOptions order by 1)
        loop
            sql_query := sql_query ||
                ' , min(case when OptionID = '''||x.OptionID||''' then 1 else null end) as Option_'||x.OptionID;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from PersonOptions group by personid order by personid';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

Then you return the results, you will use:

variable x refcursor
exec dynamic_pivot_po(:x)
print x

The results are the same with all versions:

| PERSONID | OPTIONA | OPTIONB | OPTIONC |
------------------------------------------
|        1 |       1 |       1 |       0 |
|        2 |       0 |       0 |       1 |
|        3 |       0 |       1 |       0 |
|        4 |       1 |       0 |       1 |

Pivot Table – How to Handle Multiple Dynamic Rows

Simply get MAX value of Towns and Cities grouped by Country.

SELECT Country, 
       MAX(town1) Town1, MAX(City1) City1, 
       MAX(town2) Town2, MAX(City2) City2, 
       MAX(town3) Town3, MAX(City3) City3, 
       MAX(town4) Town4, MAX(City4) City4, 
       MAX(town5) Town5, MAX(City5) City5
FROM (
      SELECT Country, 
             City, 
             'City' + CAST(ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Country) AS varchar(10)) ColumnSequence,
             Town, 
             'Town'+ CAST(ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Country) AS varchar(10)) ColumnSequence2 
      FROM Countries
     ) Temp
PIVOT (MAX(City) FOR ColumnSequence IN (City1, City2, City3, City4, City5)) Piv
PIVOT (MAX(Town) FOR ColumnSequence2 IN (town1, town2, town3, town4, town5)) Piv
GROUP BY Country
GO

Country | Town1     | City1     | Town2      | City2      | Town3  | City3      | Town4  | City4      | Town5 | City5
:------ | :-------- | :-------- | :--------- | :--------- | :----- | :--------- | :----- | :--------- | :---- | :----
India   | hyder     | Hyderabad | Bang       | Bangalore  | Del    | New Delhi  | townin | India Town | null  | null 
UK      | chelsea   | London    | sunderland | Birmingham | United | Manchester | null   | null       | null  | null 
USA     | New Jersy | New York  | houst      | Houston    | Dally  | Dallas     | null   | null       | null  | null 

Warning: Null value is eliminated by an aggregate or other SET operation.

dbfiddle here

Best Answer

Related Solutions

How to Pivot Rows into Multiple Columns in Oracle

Pivot Table – How to Handle Multiple Dynamic Rows

Related Question