DECODE function instead of PIVOT:CREATE TABLE po_table (
po_num NUMBER,
terms_type NUMBER,
terms_description VARCHAR2(20)
);
INSERT INTO po_table VALUES(10, 1, 'Desc-10-1');
INSERT INTO po_table VALUES(10, 2, 'Desc-10-2');
INSERT INTO po_table VALUES(10, 3, 'Desc-10-3');
INSERT INTO po_table VALUES(20, 1, 'Desc-20-1');
INSERT INTO po_table VALUES(20, 3, 'Desc-20-3');
INSERT INTO po_table VALUES(30, NULL, NULL);
COMMIT;
SELECT
po_num,
MAX(DECODE(terms_type, 1, terms_type)) AS terms1,
MAX(DECODE(terms_type, 1, terms_description)) AS terms1Desc,
MAX(DECODE(terms_type, 2, terms_type)) AS terms2,
MAX(DECODE(terms_type, 2, terms_description)) AS terms2Desc,
MAX(DECODE(terms_type, 3, terms_type)) AS terms3,
MAX(DECODE(terms_type, 3, terms_description)) AS terms3Desc
FROM
po_table
GROUP BY po_num
ORDER BY po_num;
Output:
PO_NUM TERMS1 TERMS1DESC TERMS2 TERMS2DESC TERMS3 TERMS3DESC
---------- ------- ------------ ------- ------------ ------- ----------
10 1 Desc-10-1 2 Desc-10-2 3 Desc-10-3
20 1 Desc-20-1 3 Desc-20-3
30
https://stackoverflow.com/questions/19812930/transpose-rows-to-columns-in-oracle-10g
