Hanseman
Hanseman

Reputation: 25

Categorize by columns

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

Answers (2)

user2672165
user2672165

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

Yaroslav Shabalin
Yaroslav Shabalin

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;

SQLFiddle.

Upvotes: 1

Related Questions