My current result:
Column A | Column B | Column B |
---|---|---|
01 | AB | col_amount |
01 | AB | col_amount |
01 | AB | col_amount |
02 | OA | col_amount |
03 | OE | col_amount |
03 | OE | col_amount |
04 | DB | col_amount |
I want to this output:
Column A | Column B | Column B |
---|---|---|
01 | AB | col_amount |
col_amount | ||
col_amount | ||
02 | OA | col_amount |
03 | OE | col_amount |
col_amount | ||
04 | DB | col_amount |
If you do not have a client application that supports the BREAK
command (which is not an SQL command but is a command for the SQL*Plus client or other similar clients) then you can use the ROW_NUMBER
analytic function and CASE
expressions:
SELECT CASE a_rn WHEN 1 THEN column_a END AS column_a,
CASE b_rn WHEN 1 THEN column_b END AS column_b,
column_c
FROM (
SELECT column_a,
column_b,
column_c,
ROW_NUMBER() OVER (PARTITION BY column_a ORDER BY column_b, column_c)
AS a_rn,
ROW_NUMBER() OVER (PARTITION BY column_a, column_b ORDER BY column_c)
AS b_rn
FROM table_name
)
Which, for the sample data:
CREATE TABLE table_name (Column_A, Column_B, Column_C) AS
SELECT '01', 'AB', 'col_amount' FROM DUAL UNION ALL
SELECT '01', 'AB', 'col_amount' FROM DUAL UNION ALL
SELECT '01', 'AB', 'col_amount' FROM DUAL UNION ALL
SELECT '02', 'OA', 'col_amount' FROM DUAL UNION ALL
SELECT '03', 'OE', 'col_amount' FROM DUAL UNION ALL
SELECT '03', 'OE', 'col_amount' FROM DUAL UNION ALL
SELECT '04', 'DB', 'col_amount' FROM DUAL;
Outputs:
COLUMN_A | COLUMN_B | COLUMN_C |
---|---|---|
01 | AB | col_amount |
null | null | col_amount |
null | null | col_amount |
02 | OA | col_amount |
03 | OE | col_amount |
null | null | col_amount |
04 | DB | col_amount |
Note: In Oracle, NULL
and an empty string are identical.