CODEWITHSUNDEEP
sqloracle-databasepivot
Moshe
Moshe

Reputation: 65

How to use pivot to select and flatten table?

I'm trying to select from a table that essentially is a hierarchy of groups and fields in each group. Each row has a group id column and I'm trying to flatten it into rows of each group id and their fields.

For example
group id | field1
1 | a
1 | b
1 | a
1 | b
2 | c
2 | d
2 | c
2 | d
3 | e
3 | f
3 | g
3 | e
3 | f
3 | g
4 | h

It is guaranteed that a group will map to the same fields values so group 1 will always have the same number of rows with field 'a' as with field 'b'.

The target is this:

group id | field1 | field2 | field 3
1 | a | b | null
2 | c | d | null
3 | e | f | g
4 | h | null | null

I have been playing with over (order by group id) but I haven't made any progress with that or pivots either.

Upvotes: 0

Views: 180

Answers (2)

Karthik
Karthik

Reputation: 99

Not sure this will solve your problem. If you are generating any report then you can use LISTAGG function.

select listagg( field_1 , ',') within group (order by group_id)
from (
select distinct group_id, field_1 from table
);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I wouldn't use pivot. I would use conditional aggregation and dense_rank():

select group_id,
       max(case when seqnum = 1 then field1 end) as field1,
       max(case when seqnum = 2 then field1 end) as field2,
       max(case when seqnum = 3 then field1 end) as field3
from (select t.*,
             dense_rank() over (partition by group_id order by field1) as seqnum
      from t
     ) t
group by group_id

Upvotes: 1

Related Questions