CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloper
user2281858
user2281858

Reputation: 1997

Complex Pivot like query in Oracle

I am having trouble creating a complex PL SQL query.

This is the Table - SQL Fiddle

ColumnA ColumnB ColumnC ColumnD ColumnE
A   Simple1    Para     Red     121
A   Simple1    Para     Blue    122
B   Simple2    Para     Red     123
B   Simple2    Para     Blue    124
C   Simple3    Para     Red     125
C   Simple3    Para     Blue    126
D   Simple4    Para     Red     127
D   Simple4    Para2    Blue    128
D   Simple4    Para3    Green   129

And I am expecting an output like this.

ColumnA ColumnB ColumnC Red Blue    Green
A   Simple1     Para    121 122     Null
B   Simple2     Para    123 124     Null
C   Simple3     Para    125 126     Null
D   Simple4     Para    127 128     129
D   Simple4     Para2   127 128     129
D   Simple4     Para3   127 128     129

Upvotes: 1

Views: 1145

Answers (2)

Aramillo
Aramillo

Reputation: 3216

You can also use pivot, with analytic functions:

select columna,columnb,columnc,max(red) over (partition by columna,columnb), 
max(green) over (partition by columna,columnb), 
max(blue) over (partition by columna,columnb)
from (
select * from t pivot (max(columne) for columnd in ('Red' as red,'Green' as green,
'Blue' as blue) ))
order by 1,2,3

Upvotes: 4

sgeddes
sgeddes

Reputation: 62831

Assuming I'm understanding your requirements, given your desired results, you want to group the red, blue and green columns by columna and columnb, not including columnc in the grouping.

To create the pivot, you can use max and case. Then you can join those results back to the original table using the first 2 columns and distinct:

SELECT DISTINCT
       t1.columna, 
       t1.columnb,
       t1.columnc,
       t2.red,
       t2.blue,
       t2.green
FROM Table1 t1
  JOIN (
      SELECT 
          ColumnA, 
          ColumnB, 
          max(case when columnd = 'Red' then columne end) red,
          max(case when columnd = 'Blue' then columne end) blue,
          max(case when columnd = 'Green' then columne end) green
      FROM Table1
      GROUP BY  columna, columnb
    ) t2 ON t1.columna = t2.columna AND t1.columnb = t2.columnb
ORDER BY t1.columna, t1.columnb, t1.columnc

Upvotes: 4

Related Questions