Multiple rows to multiple columns for co-applicant name and address for a unique ID

Question

This is the query

SELECT b.ID, e.customername AS "Applicant name",
       f.address AS "Applicant address",
       x.customername AS "Co-Applicant name",
       x.address AS "Co-Applicant address"
  FROM table_1 b,
       table_2 e,
       table_3 f,
       (SELECT b.customername, g.agreementid, a.address
          FROM table_2 g, table_4 x, table_2 b, table_3 a
         WHERE g.ID = x.ID
           AND b.customerid = x.custid
           AND b.customerid = a.custid
           AND x.flag <> 'G') x
 WHERE b.custid = e.customerid
   AND f.custid = b.lesseeid
   AND f.bptype = 'LS'
   AND f.mailingaddress = 'Y'
   AND b.ID = x.ID
   AND b.ID='101'  

The data is coming in below format.

+-----+-------+----------+--------------+----------+
| ID  | name  | address  | co-applicant | address  |
+-----+-------+----------+--------------+----------+
| 101 | aamir | address1 | rahul        | London   |
| 101 | aamir | address1 | vijay        | Paris    |
| 101 | aamir | address1 | sanjay       | New York |
+-----+-------+----------+--------------+----------+

I need the data in below format

![ID name address name_1 address name_2 address 101 aamir address1 rahul London vijay Paris
102 Anil address2 Suyash Mumbai Rajesh Delhi Prakash Kolkata]1

Answer 1

You can use PIVOT as following:

SQL> WITH DATAA AS
  2  (
  3  SELECT 101 ID, 'aamir' name, 'address1' address, 'rahul' co_applicant, 'london' co_address FROM DUAL UNION ALL
  4  SELECT 101, 'aamir', 'address1', 'vijay', 'Paris' FROM DUAL UNION ALL
  5  SELECT 101, 'aamir', 'address1', 'sanjay', 'New York' FROM DUAL
  6  )
  7  -- YOUR QUERY STARTS FROM HERE
  8  SELECT * FROM
  9      (
 10          SELECT
 11              T.*,
 12              ROW_NUMBER() OVER(ORDER BY NULL) AS RN
 13          FROM DATAA T
 14      ) PIVOT (
 15          MAX ( CO_APPLICANT ) AS NAME, MAX ( CO_ADDRESS ) AS ADDRESS
 16          FOR RN IN ( 1, 2,3 )
 17      );

        ID NAME  ADDRESS  1_NAME 1_ADDRES 2_NAME 2_ADDRES 3_NAME 3_ADDRES
---------- ----- -------- ------ -------- ------ -------- ------ --------
       101 aamir address1 rahul  london   vijay  Paris    sanjay New York

SQL>

Note: It will generate only 3 combinations of name and address as oracle do not allow dynamic columns in the query. If there is more than 3 co-applicant exist then it will take only data of 3 co-applicants.

-- UPDATE --

Use PARTITION BY clause in ROW_NUMBER if you multiple IDs as following:

SQL> WITH DATAA AS
  2      (
  3      SELECT 101 ID, 'aamir' name, 'address1' address, 'rahul' co_applicant, 'london' co_address FROM DUAL UNION ALL
  4      SELECT 101, 'aamir', 'address1', 'vijay', 'Paris' FROM DUAL UNION ALL
  5      SELECT 101, 'aamir', 'address1', 'sanjay', 'New York' FROM DUAL UNION ALL
  6      SELECT 102 ID, 'Tejash' name, 'address2' address, 'chetan' co_applicant, 'london' co_address FROM DUAL UNION ALL
  7      SELECT 102, 'Tejash', 'address2', 'nirav', 'Paris' FROM DUAL UNION ALL
  8      SELECT 102, 'Tejash', 'address2', 'pulkit', 'New York' FROM DUAL
  9      )
 10      -- YOUR QUERY STARTS FROM HERE
 11      SELECT * FROM
 12          (
 13             SELECT
 14                 T.*,
 15                 ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NULL) AS RN
 16             FROM DATAA T
 17         ) PIVOT (
 18             MAX ( CO_APPLICANT ) AS NAME, MAX ( CO_ADDRESS ) AS ADDRESS
 19             FOR RN IN ( 1, 2,3 )
 20         );

        ID NAME   ADDRESS  1_NAME 1_ADDRES 2_NAME 2_ADDRES 3_NAME 3_ADDRES
---------- ------ -------- ------ -------- ------ -------- ------ --------
       101 aamir  address1 rahul  london   vijay  Paris    sanjay New York
       102 Tejash address2 chetan london   nirav  Paris    pulkit New York

SQL>

Cheers!!