Reputation: 25
Have no idea what I'm to name this request so therefore I have not found any answers to it. Basically a common statement like:
SELECT A,B,C,D,E FROM TABLE
Example result:
A B C D E
1 1 2 3 4
1 2 3 4 5
1 2 7 8 9
2 1 4 5 6
How do I 'categorize' by certain columns (in example A and B) so column values are omitted?
Preferred result:
A B C D E
1 1 2 3 4
1 2 3 4 5
7 8 9
2 1 4 5 6
Upvotes: 1
Views: 62
Reputation: 3049
In your case I guess it would make sense to have the result ordered by A,B? In that case you could use:
SELECT DECODE(RN,1,A,NULL) AS A,
DECODE(RN,1,B,NULL) AS B,
C,
D,
E
FROM
(SELECT A,
B,
row_number() over (partition BY A,B order by A,B) AS RN,
C,
D,
E
FROM
(SELECT * FROM TEST_TABLE ORDER BY A,B
)
);
Upvotes: 1
Reputation: 1644
You can use LAG
analytic function to access previous row values. See below example:
SELECT case
when LAG(a, 1, NULL)
OVER(ORDER BY a, b, c, d, e) = a and LAG(b, 1, NULL)
OVER(ORDER BY a, b, c, d, e) = b then
null
else
a
end new_a,
case
when LAG(a, 1, NULL)
OVER(ORDER BY a, b, c, d, e) = a and LAG(b, 1, NULL)
OVER(ORDER BY a, b, c, d, e) = b then
null
else
b
end new_b,
c,
d,
e
FROM t_table t
ORDER BY a, b, c, d, e;
Upvotes: 1