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.