Xrhrft

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.

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

)