根据特定的列值将SQL列拆分为多个列 [英] Splitting SQL Columns into Multiple Columns Based on Specific Column Value

问题描述

我想写一个查询，列出我们在大学所提供的课程.一个程序至少包括一个主要的，可能包括一个选项"，一个专业"和一个子专业".这四个元素中的每个元素都有详细的代码，该代码将它们与专业相关.

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 |
+----------------+-------------+----------------+-------------------+------+

到目前为止，我已经尝试创建四个连接在此代码"上的查询，每个查询均基于不同的"program_level"进行选择.但是，这些字段无法正确组合.

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.

推荐答案

我找不到比这更简单的了:

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'

更正了输入错误"Speciality"，它现在应该可以使用.

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

这篇关于根据特定的列值将SQL列拆分为多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！