Ryan Barker Ryan Barker - 1 month ago 16
SQL Question

Oracle SQL Automated Rows to Column Pivot

This is something simple that I guarantee I am over-thinking. I have an input data set with monthly data that looks like:

MTD | ID | State
-----------------------
Jan-16 | 1 | A
Feb-16 | 1 | B
Mar-16 | 1 | A
Jan-16 | 2 | C
Feb-16 | 2 | B
Mar-16 | 2 | A


MTD is a date field and each month is represented by the first of that month (Ex: April 2016 = 01-Apr-16).

I need to write a query that returns:

ID | Jan State | Feb State | Mar State | (Repeat for each month)
------------------------------------------------------------------------
1 | A | B | A | ...
2 | C | B | A | ...


Because new data is added per month, I want to automate the query so I don't have to edit it when a new month of data comes out.

Is what I'm trying to do possible?

Answer

See Comments to original question - the OP indicated he will be happy with a solution for a known set of values in MTD column.

Demo (assuming the values in the MTD column are known beforehand):

with
     inputs ( mtd, id, state ) as (
       select 'Jan-16', 1, 'A' from dual union all
       select 'Feb-16', 1, 'B' from dual union all
       select 'Mar-16', 1, 'A' from dual union all
       select 'Jan-16', 2, 'C' from dual union all
       select 'Feb-16', 2, 'B' from dual union all
       select 'Mar-16', 2, 'A' from dual
     )
select *
from   inputs
pivot (max(state) for mtd in ('Jan-16' as jan_state, 'Feb-16' as feb_state,
                                                     'Mar-16' as mar_state))
;

Output:

ID JAN_STATE FEB_STATE MAR_STATE
--- -------- --------- ---------
 1 A         B         A
 2 C         B         A