Taking tall making wide

Question

I am working in Oracle. I want SQL to take a table of students with student_id and class columns and make it wide, with the classes sorted in the columns. Example table:

SID | CLASS 
----+------
1   | ENG
1   | BIO
2   | MAT
2   | BIO
1   | MAT

I would like output to be:

SID | CLASS1 | CLASS2 | CLASS3
----+--------+--------+-------
1   | BIO    | ENG    | MAT
2   | BIO    | MAT    |

An added feature would be if I can fill blanks on classes with NA.

Usually there would be an unknown number of classes and SQL is not made to retrieve an unknown number of columns. It gets the data in rows and you'd use your GUI layer (i.e. an app or Website) to care about the desired display in a grid. — Thorsten Kettner, Oct 01 '18 at 22:06
What makes a class class #1 or #2 or #3? Do you want alphabetic order? — Thorsten Kettner, Oct 01 '18 at 22:12
You could generate a CSV off that. It depends on the purpose. Like this. https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle — JOTN, Oct 01 '18 at 22:21
If you know that there will always be three or fewer classes, then this is just a bad idea. If there can ever be more than three classes this is a very bad idea. So, the important question is ***why*** do you want do do something different from normal relational database patterns? — MatBailie, Oct 01 '18 at 22:30

score 0 · Answer 1 · answered Oct 05 '18 at 19:44

So first, full disclosure. I was actually trying to pull race values for patients, I just did classes to make it a bit more relatable. Also the races are hierarchical, so alpha sort did not work, hence the numeric value at the front (to be discarded later). Here is my solution, using a 'with' temp table and assigning it different aliases in several joins

CREATE OR REPLACE FORCE VIEW schema_name.VW_PAT_RACE AS

with pr as
(
select
    r.*
    , rank() over(partition by r.patient_id order by r.r1) as r_num
from
    (
    Select
        patient_id
        , race_c
        , line
        , case
            WHEN race_c = 20 THEN '1HP' 
            WHEN race_c = 14 THEN '2IN' 
            WHEN race_c = 15 THEN '3AS' 
            WHEN race_c = 12 THEN '4BA' 
            WHEN race_c = 11 THEN '5WH' 
            ELSE '7UN'                         /* And always add an Unknown bucket */
        END AS R1
    from
        some_schema.patient_race 
    order by
        patient_id
        , r1
    ) r
)
select distinct
    pr0.patient_id
    , CAST(COALESCE(substr(pr1.r1, 2), 'NA') AS VARCHAR2 (2)) as RACE1
    , CAST(COALESCE(substr(pr2.r1, 2), 'NA') AS VARCHAR2 (2)) as RACE2
    , CAST(COALESCE(substr(pr3.r1, 2), 'NA') AS VARCHAR2 (2)) as RACE3
    , CAST(COALESCE(substr(pr4.r1, 2), 'NA') AS VARCHAR2 (2)) as RACE4
    , CAST(COALESCE(substr(pr5.r1, 2), 'NA') AS VARCHAR2 (2)) as RACE5
from 
    pr pr0
    left join pr pr1
        on pr0.patient_id = pr1.patient_id
        and pr1.r_num = 1
    left join pr pr2
        on pr0.patient_id = pr2.patient_id
        and pr2.r_num = 2
    left join pr pr3
        on pr0.patient_id = pr3.patient_id
        and pr3.r_num = 3 
    left join pr pr4
        on pr0.patient_id = pr4.patient_id
        and pr4.r_num = 4
    left join pr pr5
        on pr0.patient_id = pr5.patient_id
       and pr5.r_num = 5
;

Obviously, I could have skipped PR0 and pulled id and race and patient_id from the first from table but somehow this seemed to clarify where each race value came from. I also apologize to old hat programmers, for my highly stylized code but it helps me when I come back to do maintenance. Feed back welcome!

Taking tall making wide

1 Answers1