CODEWITHSUNDEEP
sqloracle-databasepivot
Rimesh Patil
Rimesh Patil

Reputation: 1

How to transpose colums data into rows in oracle plsql

My table is like below:

MOBILE  ADDRESS_1   ADDRESS_2   ADDRESS_3   ADDRESS_TYPE
12345   ABC         EFG         HUJ         RS
12345   hjk         qqq         asd         OF
12345   jkh         sss         qwe         PR

Output I want is like:

MOBILE ADDRESS_1_RS ADDRESS_2_RS ADDRESS_3_RS  ADDRESS_1_OF ADDRESS_2_OF ADDRESS_3_OF ADDRESS_1_PR ADDRESS_2_PR ADDRESS_3_PR
12345  ABC          EFG          HUJ           hjk          qqq           asd          jkh         sss          qwe

Upvotes: 0

Views: 427

Answers (2)

MT0
MT0

Reputation: 168001

You can PIVOT:

SELECT mobile,
       rs_address_1 AS address_1_rs,
       rs_address_2 AS address_2_rs,
       rs_address_3 AS address_3_rs,
       of_address_1 AS address_1_of,
       of_address_2 AS address_2_of,
       of_address_3 AS address_3_of,
       pr_address_1 AS address_1_pr,
       pr_address_2 AS address_2_pr,
       pr_address_3 AS address_3_pr
FROM   table_name
PIVOT (
  MAX( address_1 ) AS address_1,
  MAX( address_2 ) AS address_2,
  MAX( address_3 ) AS address_3
  FOR address_type IN (
    'RS' AS rs,
    'OF' AS "OF",
    'PR' AS pr
  )
)

Which, for your sample data:

CREATE TABLE table_name ( MOBILE, ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_TYPE ) AS
SELECT 12345, 'ABC', 'EFG', 'HUJ', 'RS' FROM DUAL UNION ALL
SELECT 12345, 'hjk', 'qqq', 'asd', 'OF' FROM DUAL UNION ALL
SELECT 12345, 'jkh', 'sss', 'qwe', 'PR' FROM DUAL;

Outputs:

MOBILE | ADDRESS_1_RS | ADDRESS_2_RS | ADDRESS_3_RS | ADDRESS_1_OF | ADDRESS_2_OF | ADDRESS_3_OF | ADDRESS_1_PR | ADDRESS_2_PR | ADDRESS_3_PR
-----: | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :-----------
 12345 | ABC          | EFG          | HUJ          | hjk          | qqq          | asd          | jkh          | sss          | qwe

db<>fiddle here

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65288

You can use conditional aggregation in order to get the pivoted result such as

SELECT mobile,
       MAX(CASE WHEN address_type = 'RS' THEN address_1 END) AS "address_1_RS",
       MAX(CASE WHEN address_type = 'RS' THEN address_2 END) AS "address_2_RS",
       MAX(CASE WHEN address_type = 'RS' THEN address_3 END) AS "address_3_RS",

       MAX(CASE WHEN address_type = 'OF' THEN address_1 END) AS "address_1_OF",
       MAX(CASE WHEN address_type = 'OF' THEN address_2 END) AS "address_2_OF",
       MAX(CASE WHEN address_type = 'OF' THEN address_3 END) AS "address_3_OF",
       
       MAX(CASE WHEN address_type = 'PR' THEN address_1 END) AS "address_1_PR",
       MAX(CASE WHEN address_type = 'PR' THEN address_2 END) AS "address_2_PR",
       MAX(CASE WHEN address_type = 'PR' THEN address_3 END) AS "address_3_PR"              
  FROM t
 GROUP BY mobile

Demo

Upvotes: 1

Related Questions