How to create a dynamic pivot table w/ created columns using Oracle SQL 11g

Question

I have a little on the job Oracle SQL training for the past two years which has gotten me by for the most part in my job (it’s not a huge part of my work beyond simple select, delete, update, pivot, etc.). I have recently received a requirement that is very complex and way above my technical ability, unfortunately I don’t have access to an available professional DBA to get some help on this. I need an oracle SQL script that will bring back the correct data and format.

The query that I made brings back the information that is needed:

SELECT T1.ATTRIBUTE_1,
T1.ATTRIBUTE_2,
T1.ATTRIBUTE_3,
T1.COMMON_IDENTIFIER,
T2.ERROR,
T2.STATUS,
T2.TYPE
T2.TYPE_NUMBER
FROM TABLE_1 T1,
TABLE_2 T2
WHERE T1.COMMON_IDENTIFIER = T2.COMMON_IDENTIFIER;

This is much simpler than the results I'm looking for but has all the data I need and it gives results like this (the results that I'm looking for will be dictated by a third table):

ATTRIBUTE_1   ATTRIBUTE_2 ATTRIBUTE_3 COMMON_IDENTIFER    ERROR   STATUS  TYPE    TYPE_NUMBER
  BLAH          BLAH          BLAH            A1            E       S    TYPE i    #######
  BLAH          BLAH          BLAH            A1            E       S    TYPE ii   #######
  BLAH          BLAH          BLAH            A1            E       S    TYPE iii  #######
  BLAH          BLAH          BLAH            A5            E       S    TYPE i    #######
  BLAH          BLAH          BLAH            A5            E       S    TYPE ii   #######
  BLAH          BLAH          BLAH            A7            E       S    TYPE i    #######
  BLAH          BLAH          BLAH            A1            E       S    TYPE iv   #######

I have combed over the internet and found a simple example of what type of behavior I am looking for:

Pivoting Data twice with dynamic sql and custom column names

I have hit a wall trying to translate this from SQL server to oracle but its been slow going and I will eventually need to combine the results from that query with results from another table.

I created a query that gets me close to the solution but its missing ERROR and STATUS data for a given TYPE and respective TYPE_NUMBER:

SELECT * FROM (
   SELECT T1.COMMON_IDENTIFIER,
   T2.TYPE,
   T2.TYPE_NUMBER,
   T2.ERROR,
   T2.STATUS
   FROM TABLE_1 T1, TABLE_2 T2
   WHERE T1.COMMON_IDENTIFIER = T2.COMMON_IDENTIFIER
   AND T2.TYPE IN ('TYPE i', 'TYPE ii', 'TYPE iii', 'TYPE iv'))
PIVOT (
   MAX(TYPE_NUMBER) FOR TYPE IN ('TYPE i', 'TYPE ii', 'TYPE iii', 'TYPE iv')
   )

With results looking like this:

COMMON_IDENTIFIER STATUS  ERROR   TYPE i   TYPE ii  TYPE iii  TYPE iv
        A1          S       E     #######  #######  #######   #######
        A5          S       E     #######  #######      
        A7          S       E     #######

This is very close to what I am looking for but I am missing ERROR and STATUS data for each unique TYPE_NUMBER for a given TYPE value (TYPE i, TYPE ii, etc.). Also the column headers 'TYPE i', 'TYPE ii' etc. all need to be dynamic as their values will be able to change at any given time and are tied to a third table.

I need an oracle SQL query that will give me results that will look like this:

ATTRIBUTE_1    ATTRIBUTE_2    ATTRIBUTE_3     COMMON_IDENTIFIER   ERROR_i  STATUS_i   TYPE_i   ERROR_ii   STATUS_ii   TYPE_ii  ERROR_iii   STATUS_iii  TYPE_iii   etc.                                                                 
  BLAH            BLAH           BLAH               A1               E      S         #######       E          S        #######     E           S        #######    
  BLAH            BLAH           BLAH               A5               E       S        #######       E          S        ####### 
  BLAH            BLAH           BLAH               A7               E       S        #######

Some notes on the results I'm looking for:

-The values for the column TYPE in the first query and used as headers in the second query I'm looking for, are dictated by a third table that a user has access to and can take away or add values on the fly. e.g. in the first query there are only 4 values for the column TYPE, this can change at any time.

-The TYPE_NUMBER column value is specifically tied to the TYPE value (TYPE i, TYPE ii, etc.) along with the values for the ERROR and STATUS column. So if a user creates a new TYPE value in the third table, I need to be able to dynamically create the TYPE_XX, ERROR_XX, and STATUS_XX columns to accommodate the changes.

Like I said before this way above my technical ability and I apologize for any redundancy. I don't really have any formal training for SQL as its all been on the job, I just wanted to be as clear as possible when describing the problem and the solution I'm looking for.

Thank you in advance for any and all assistance!

Duplicate of https://stackoverflow.com/q/15491661/1509264 – MT0 Jun 07 '18 at 07:54 — MT0, Jun 07 '18 at 07:54

score 0 · Answer 1 · answered Jun 06 '18 at 18:18

The distinct ERROR and STATUS values can be accomplished fairly easily:

SELECT * FROM (
    SELECT T1.common_identifier, T2.type
         , T2.type_number, T2.error, T2.status
      FROM table_1 T1 INNER JOIN table_2 T2
        ON t1.common_identifier = t2.common_identifier
     WHERE t2.type IN ('TYPE i', 'TYPE ii', 'TYPE iii', 'TYPE iv')
) PIVOT (
    MAX(error) AS error
  , MAX(status) AS status
  , MAX(type_number) AS type
      FOR type IN ( 'TYPE i' AS i, 'TYPE ii' AS ii
                  , 'TYPE iii' AS iii, 'TYPE iv' AS iv )
)

The column names will come out a bit funky - e.g., I_ERROR instead of ERROR_I - but I assume you can fix that yourself.

The dynamic portion is more difficult; PIVOT, by itself, doesn't work with dynamic lists; you'll have to use dynamic SQL - in a PL/SQL stored procedure, for example - in order to accomplish this.

Thank you, very much @David Faber! I kind of figured this would end up as a stored procedure due to the dynamic column names. I've seen them before and have worked with them within the application I am working with but have never written one. Unfortunately the requirement that some of the columns be dynamic is pretty solid and unchangeable. Thanks again for the input! Either way I'm learning some valuable skills! — AbsurD, Jun 06 '18 at 18:25

How to create a dynamic pivot table w/ created columns using Oracle SQL 11g

1 Answers1