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.
Advertisement
Answer
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
db<>fiddle here