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 — user330315
– user330315, Commented Jun 18, 2015 at 14:22
@a_horse_with_no_name good catch, my bad :) I corrected my query below. — ttzn
– ttzn, Commented Jun 18, 2015 at 14:24

ttzn · Accepted Answer · 2015-06-18 14:24:29Z

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, 2015 at 14:24

answered Jun 18, 2015 at 13:47

ttzn

2,62324 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ky_lej Over a year ago

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

ttzn Over a year ago

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).

ky_lej Over a year ago

After the correction it works fine! Thanks so much, editing the post now.

Matt · Accepted Answer · 2015-06-18 13:33:56Z

0

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

answered Jun 18, 2015 at 13:33

Matt

15.1k28 gold badges109 silver badges161 bronze badges

3 Comments

ky_lej Over a year ago

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?

ttzn Over a year ago

This is dangerous as it relies on a specific order of the returned rows to be anything near readable.

Matt Over a year ago

@Amine Absolutely, if possible this data structure should be re done as separate columns

Lars Br. · Accepted Answer · 2015-06-23 06:07:21Z

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

Collectives™ on Stack Overflow

Splitting SQL Columns into Multiple Columns Based on Specific Column Value

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related