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.

You have `Specialty` in your sample data but use `Speciality` in the join (same for `Subspeciality`) and therefor your joins are failing. If you fix those typos, your statement works fine — , Jun 18 '15 at 14:22
@a_horse_with_no_name good catch, my bad :) I corrected my query below. — ttzn, Jun 18 '15 at 14:24

score 0 · Answer 1 · answered Jun 18 '15 at 13:33

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

thanks for the quick reply. This is essentially the same output I was getting though - is there a way to list the associated majors/previous columns in every row as in my example output? — ky_lej, Jun 18 '15 at 13:48
This is dangerous as it relies on a specific order of the returned rows to be anything near readable. — ttzn, Jun 18 '15 at 14:19
@Amine Absolutely, if possible this data structure should be re done as separate columns — Matt, Jun 18 '15 at 14:21

ttzn · Accepted Answer · 2015-06-18T14:24:29.867

0

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.

edited Jun 18 '15 at 14:24

answered Jun 18 '15 at 13:47

ttzn

2,543
22
26

I think this is the right track... however, when I run this, I get a result set with nulls for every row in the spec and subspec column. See my post edit – ky_lej Jun 18 '15 at 14:05
That's very odd. I ran the query in SQL Server using a table variable and the output is exactly the one you're looking for. I can paste the whole code for you to see, but I'd suggest you first check that the strings used in the conditions match the values stored in the database (i.e., you might have whitespace in the `program_level` fields). – ttzn Jun 18 '15 at 14:09
After the correction it works fine! Thanks so much, editing the post now. – ky_lej Jun 18 '15 at 14:26

score 0 · Answer 3 · answered Jun 23 '15 at 06:07

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

Splitting SQL Columns into Multiple Columns Based on Specific Column Value

3 Answers3

Linked