的Oracle 11g SQL行列

Question

我有两个表看起来像这样：

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

查询：

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; 

查询结果：

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 

我希望像输出这个：

REQ_ID  surname  name pname 
23000  IVANOV  IVAN V. 

我正在使用Oracle DB 11.它如何实现？

Answer 1

在Oracle 11g中，您可以使用PIVOT功能：

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; 

这也可以使用聚合函数与CASE书面表达：

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;