CODEWITHSUNDEEP
sqloracle-databaseoracle11gpivot
Artur Burnos
Artur Burnos

Reputation: 177

Oracle 11g SQL rows to columns

I have two tables that look like this:

LOAN_APP_QST (profile on lending) and
QST_PART (it set up all the fields of loan application).

Query:

SELECT LOAN_APP_QST.REQ_ID,
       LOAN_APP_QST.QST_PART_ID,
       LOAN_APP_QST.LAQ_VALUE,
       QST_PART.QP_NUM,
       QST_PART.QP_NAME
  FROM    SP_MTS.LOAN_APP_QST LOAN_APP_QST
       INNER JOIN
          SP_MTS.QST_PART QST_PART
       ON (LOAN_APP_QST.QST_PART_ID = QST_PART.QST_PART_ID)
       WHERE LOAN_APP_QST.REQ_ID = 23000;

Query Result:

REQ_ID     QST_PART_ID     LAQ_VALUE     QP_NUM      QP_NAME
23000          -1           IVANOV       surname    FirstName
23000          -2           IVAN          name      LastName
23000          -3           V.            pname     Patronymic

I would want an output like this:

REQ_ID      surname     name    pname
23000       IVANOV       IVAN   V.

I am using Oracle DB 11. How it can be implemented?

Upvotes: 0

Views: 4486

Answers (1)

Taryn
Taryn

Reputation: 247860

In Oracle 11g you can use the PIVOT function:

select *
from
(
  SELECT LOAN_APP_QST.REQ_ID,
   LOAN_APP_QST.LAQ_VALUE,
   QST_PART.QP_NUM
  FROM SP_MTS.LOAN_APP_QST LOAN_APP_QST
  INNER JOIN SP_MTS.QST_PART QST_PART
    ON (LOAN_APP_QST.QST_PART_ID = QST_PART.QST_PART_ID)
  WHERE LOAN_APP_QST.REQ_ID = 23000
) src
pivot
(
  max(LAQ_VALUE)
  for QP_NUM in (surname, name, pname)
) piv;

This can also be written using an aggregate function with a CASE expression:

SELECT LOAN_APP_QST.REQ_ID,
  max(case when QST_PART.QP_NUM = 'surname' then LOAN_APP_QST.LAQ_VALUE end) as surname,
  max(case when QST_PART.QP_NUM = 'name' then LOAN_APP_QST.LAQ_VALUE end) as name,
  max(case when QST_PART.QP_NUM = 'pname' then LOAN_APP_QST.LAQ_VALUE end) as pname
FROM    SP_MTS.LOAN_APP_QST LOAN_APP_QST
INNER JOIN SP_MTS.QST_PART QST_PART
  ON (LOAN_APP_QST.QST_PART_ID = QST_PART.QST_PART_ID)
WHERE LOAN_APP_QST.REQ_ID = 23000
group by LOAN_APP_QST.REQ_ID;

Upvotes: 1

Related Questions