Oracle convert multiple rows to column using pivot

Question

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.

Answer 1

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" )
)