Splitting SQL Columns into Multiple Columns Based on Specific Column Value

Question

I want to write a query that lists the programs we offer at my university. A program consists of at least a major, and possibly an "option", a "specialty", and a "subspecialty". Each of these four elements are detailed with an code which relates them back to the major.

One major can have zero or more options, one option can have zero or more specialties, and one specialty can have zero or more sub specialties. Conversely, a major is permitted to have no options associated with it.

In the result set, a row must contain the previous element in order to have the next one, i.e. a row will not contain a major, no option, and a specialty. The appearance of a specialty associated with a major implies that there is also an option that is associated with that major.

My problem lies in how the data is stored. All program data lies in one table that is laid out like this:

+----------------+---------------+------+
| program_name   | program_level | code |
+----------------+---------------+------+
| Animal Science | Major         |    1 |
| Equine         | Option        |    1 |
| Dairy          | Option        |    1 |
| CLD            | Major         |    2 |
| Thesis         | Option        |    2 |
| Non-Thesis     | Option        |    2 |
| Development    | Specialty     |    2 |
| General        | Subspecialty  |    2 |
| Rural          | Subspecialty  |    2 |
| Education      | Major         |    3 |
+----------------+---------------+------+

Desired output will look something like this:

+----------------+-------------+----------------+-------------------+------+
| major_name     | option_name | specialty_name | subspecialty_name | code |
+----------------+-------------+----------------+-------------------+------+
| Animal Science | Equine      |                |                   |    1 |
| Animal Science | Dairy       |                |                   |    1 |
| CLD            | Thesis      | Development    | General           |    2 |
| CLD            | Thesis      | Development    | Rural             |    2 |
| CLD            | Non-Thesis  | Development    | General           |    2 |
| CLD            | Non-Thesis  | Development    | Rural             |    2 |
| Education      |             |                |                   |    3 |
+----------------+-------------+----------------+-------------------+------+

So far I've tried to create four queries that join on this "code", each selecting based on a different "program_level". The fields aren't combining properly though.

Answer 1

I can't find simpler than this :

/* Replace @Programs with the name of your table */

SELECT majors.program_name, options.program_name, 
    specs.program_name, subspecs.program_name, majors.code
FROM @Programs majors
LEFT JOIN @Programs options 
    ON majors.code = options.code AND options.program_level = 'Option'
LEFT JOIN @Programs specs 
    ON options.code = specs.code AND specs.program_level = 'Specialty'
LEFT JOIN @Programs subspecs 
    ON specs.code = subspecs.code AND subspecs.program_level = 'Subspecialty'
WHERE majors.program_level = 'Major'

EDIT : corrected typo "Speciality", it should work now.

Answer 2

Use sub queries to build up what you want.

CODE:

SELECT(SELECT m.program_name FROM yourtable m WHERE m.program_level = 'Major' AND y.program_name = m.program_name) AS major_name,
      (SELECT o.program_name FROM yourtable o WHERE o.program_level = 'Option' AND y.program_name = o.program_name) AS Option_name,
      (SELECT s.program_name FROM yourtable s WHERE s.program_level = 'Specialty' AND y.program_name = s.program_name) AS Specialty_name,
      (SELECT ss.program_name FROM yourtable ss WHERE ss.program_level = 'Subspecialty' AND y.program_name = ss.program_name) AS Subspecialty_name, code
FROM yourtable y

OUTPUT:

major_name      Option_name Specialty_name  Subspecialty_name   code
Animal Science  (null)      (null)          (null)              1
(null)          Equine      (null)          (null)              1
(null)          Dairy       (null)          (null)              1
CLD             (null)      (null)          (null)              2
(null)          Thesis      (null)          (null)              2
(null)          Non-Thesis  (null)          (null)              2
(null)          (null)      Development     (null)              2
(null)          (null)      (null)          General             2
(null)          (null)      (null)          Rural               2
Education       (null)      (null)          (null)              3

SQL Fiddle: http://sqlfiddle.com/#!3/9b75a/2/0

Answer 3

The key to the solution here is to realize that there are actually multiple entities stored in the same table here and that those entities need to be 'extracted' first.

This can be done with a simple sub-query that filters on the program_level column.

select ma.major_name, op.option_name, sp.specialty_name,  ss.subspecialty_name, ma.code
from 
   (select code, program_name  as major_name 
    from programs where program_level = 'Major') ma
left outer join
    (select code, program_name as option_name
     from programs where program_level ='Option') op
     on ma.code = op.code    
left outer join
    (select code, program_name as specialty_name
     from programs where program_level ='Specialty') sp
    on op.code = sp.code
left outer join
    (select code, program_name as subspecialty_name
     from programs where program_level ='Subspecialty') ss
    on sp.code = ss.code
order by ma.code asc, 
      ma.major_name asc,
      op.option_name asc,
      sp.specialty_name asc,
      ss.subspecialty_name asc;

This provides you with the output you required:

MAJOR_NAME      OPTION_NAME SPECIALTY_NAME  SUBSPECIALTY_NAME   CODE
Animal Science  Dairy       ?               ?                   1   
Animal Science  Equine      ?               ?                   1   
CLD             Non-Thesis  Development     General             2   
CLD             Non-Thesis  Development     Rural               2   
CLD             Thesis      Development     General             2   
CLD             Thesis      Development     Rural               2   
Education       ?           ?               ?                   3   

Cheers, Lars