0

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.

1 Answer 1

0

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" )
)
1
  • thanks for your response. But I have huge data table to convert. I tired using code but it is not working as expected. here is the sql fiddle sqlfiddle.com/#!4/8a71c/1
    – heye
    Sep 27, 2018 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.