英文:
How to merge columns and transpose in SQL?
问题
我正在使用自定义的DBMS（符合SQL 2011标准），尝试将多个列合并为单个列，如下所示，但在语法方面遇到了困难：
当前表格：
------------------------------------------
| ID | setting_A | setting_B | setting_C |
------------------------------------------
| 1 | ON | OFF | UNKNOWN |
------------------------------------------
期望的查询结果：
------------------------------
| ID | Setting | Value |
------------------------------
| 1 | A | ON |
------------------------------
| 1 | B | OFF |
------------------------------
| 1 | C | UNKNOWN |
------------------------------
我已经尝试了各种IF和CASE语句，但遇到了难题。任何帮助将不胜感激。
英文:
I'm working with a custom DBMS (compliant with SQL 2011 Standard), and am trying to combine multiple columns into a single column like so, but am struggling with the syntax:
Current table:
------------------------------------------
| ID | setting_A | setting_B | setting_C |
------------------------------------------
| 1 | ON | OFF | UNKNOWN |
------------------------------------------
Desired query output:
------------------------------
| ID | Setting | Value |
------------------------------
| 1 | A | ON |
------------------------------
| 1 | B | OFF |
------------------------------
| 1 | C | UNKNOWN |
------------------------------
I've tried various IF and CASE statements, but have hit a wall. Any help would be appreciated.
答案1
得分: 1
你可以使用 union all 进行解旋（unpivot）：
select id, 'A' setting, setting_A value from mytable
union all select id, 'B', setting_B from mytable
union all select id, 'C', setting_C from mytable
这假设 setting_A、setting_B 和 setting_C 具有相同的数据类型（否则，在组合结果集之前，您需要进行数据类型转换以使其一致）。
英文:
You can unpivot with union all:
select id, 'A' setting, setting_A value from mytable
union all select id, 'B', setting_B from mytable
union all select id, 'C', setting_C from mytable
This assumes that setting_A, setting_B and setting_C are of the same datatype (otherwise, you need conversions to align the datatypes before combining the resultsets).
<pre>
| id | setting | value |
|---|---|---|
| 1 | A | ON |
| 1 | B | OFF |
| 1 | C | UNKNOWN |
| </pre> |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论