Change Two Rows Into Two Columns-CodePudding

Oracle newbie here just trying to learn something.

I have a query that returns two rows per ID:

SELECT B1_ALT_ID, B1_CHECKLIST_COMMENT
FROM PERMIT
WHERE (B1_CHECKBOX_DESC = 'Certificate Number'
OR B1_CHECKBOX_DESC = 'DIF_Category');

Current Output

Instead of two rows, how can I put the two varying values in the B1_CHECKLIST_COMMENT column into two separate columns?

Visually, here's what I'm looking to achieve:

Desired Output

B1_ALT_ID           B1_CHECKLIST_1          B1_CHECKLIST_2
DIF13-0001          27654                   Fiber
DIF13-0002          1380                    Water
DIF13-0003          736                     Library

I'm using Oracle version 12.1.

CodePudding user response：

You can use PIVOT:

SELECT B1_ALT_ID, B1_CHECKLIST_1, B1_CHECKLIST_2
FROM PERMIT
PIVOT (
  MAX(B1_CHECKLIST_COMMENT)
  FOR B1_CHECKBOX_DESC IN (
    'Certificate Number' AS B1_CHECKLIST_1,
    'DIF_Category'       AS B1_CHECKLIST_2
  )
);

Or conditional aggregation:

SELECT B1_ALT_ID,
       MAX(
         CASE B1_CHECKBOX_DESC
         WHEN 'Certificate Number'
         THEN B1_CHECKLIST_COMMENT
         END
       ) AS B1_CHECKLIST_1,
       MAX(
         CASE B1_CHECKBOX_DESC
         WHEN 'DIF_Category'
         THEN B1_CHECKLIST_COMMENT
         END
       ) AS B1_CHECKLIST_2
FROM   PERMIT
GROUP BY B1_ALT_ID;

Which, for the sample data:

CREATE TABLE permit (b1_alt_id, b1_checklist_comment, b1_checkbox_desc) AS
SELECT 'DIF13-001', '27654', 'Certificate Number' FROM DUAL UNION ALL
SELECT 'DIF13-001', 'Fiber', 'DIF_Category'       FROM DUAL UNION ALL
SELECT 'DIF13-002', '1380', 'Certificate Number'  FROM DUAL UNION ALL
SELECT 'DIF13-002', 'Water', 'DIF_Category'       FROM DUAL UNION ALL
SELECT 'DIF13-003', '736', 'Certificate Number'   FROM DUAL UNION ALL
SELECT 'DIF13-003', 'Library', 'DIF_Category'     FROM DUAL;

Both output:

B1_ALT_ID B1_CHECKLIST_1 B1_CHECKLIST_2

DIF13-003 736 Library

DIF13-001 27654 Fiber

DIF13-002 1380 Water

B1_ALT_ID	B1_CHECKLIST_1	B1_CHECKLIST_2
DIF13-003	736	Library
DIF13-001	27654	Fiber
DIF13-002	1380	Water

db<>fiddle here