如何将表格的前两列堆叠为一列? [英] How do I stack the first two columns of a table into a single column?
问题描述
我想将表的两列堆叠为一列.因此，例如，如果源表是:
| Id | Code_1 | Code_2 | Other |
|----|---------|---------|-------|
| 1 | Value_1 | Value_2 | Row_1 |
| 2 | Value_3 | (null) | Row_2 |
| 3 | (null) | Value_4 | Row_3 |
| 4 | (null) | (null) | Row_4 |
| 5 | Value_5 | (null) | Row_5 |
I'd like to stack two columns of a table into a single column. So for example, if the source table is:
| Id | Code_1 | Code_2 | Other |
|----|---------|---------|-------|
| 1 | Value_1 | Value_2 | Row_1 |
| 2 | Value_3 | (null) | Row_2 |
| 3 | (null) | Value_4 | Row_3 |
| 4 | (null) | (null) | Row_4 |
| 5 | Value_5 | (null) | Row_5 |
然后目标表应为:
| Id | Code | Other |
|----|---------|-------|
| 1 | Value_1 | Row_1 |
| 2 | Value_2 | Row_1 |
| 3 | Value_3 | Row_2 |
| 4 | Value_4 | Row_3 |
| 5 | Value_5 | Row_5 |
Then the target table should be:
| Id | Code | Other |
|----|---------|-------|
| 1 | Value_1 | Row_1 |
| 2 | Value_2 | Row_1 |
| 3 | Value_3 | Row_2 |
| 4 | Value_4 | Row_3 |
| 5 | Value_5 | Row_5 |
请注意，Other列仅重复其值，但仅当Code_1和Code_2中存在非空值时.
Notice that the Other column just duplicates its values, but only when there's a non-null value in Code_1 and Code_2.
我试图使用PIVOT语句cos，我本质上是试图(几乎)堆叠每一行的转置，但是我不知道如何正确地对其进行编排.
I tried to use a PIVOT statement cos I'm essentially trying to (almost) stack transposes of each row, but I can't figure out how to orchestrate it properly.
我的DDL在下面:
CREATE TABLE Source (
Id INT IDENTITY(1, 1) PRIMARY KEY,
Code_1 VARCHAR(10),
Code_2 VARCHAR(10),
Other VARCHAR(10)
);
INSERT INTO Source VALUES ('Value_1', 'Value_2', 'Row_1'), ('Value_3', NULL, 'Row_2'), (NULL, 'Value_4', 'Row_3'), (NULL, NULL, 'Row_4'), ('Value_5', NULL, 'Row_5');
CREATE TABLE Source (
Id INT IDENTITY(1, 1) PRIMARY KEY,
Code_1 VARCHAR(10),
Code_2 VARCHAR(10),
Other VARCHAR(10)
);
INSERT INTO Source VALUES ('Value_1', 'Value_2', 'Row_1'), ('Value_3', NULL, 'Row_2'), (NULL, 'Value_4', 'Row_3'), (NULL, NULL, 'Row_4'), ('Value_5', NULL, 'Row_5');
CREATE TABLE Target (
Id INT IDENTITY(1, 1) PRIMARY KEY,
Code VARCHAR(10),
Other VARCHAR(10)
);
INSERT INTO Target VALUES ('Value_1', 'Row_1'), ('Value_2', 'Row_1'), ('Value_3', 'Row_2'), ('Value_4', 'Row_3'), ('Value_5', 'Row_5')
CREATE TABLE Target (
Id INT IDENTITY(1, 1) PRIMARY KEY,
Code VARCHAR(10),
Other VARCHAR(10)
);
INSERT INTO Target VALUES ('Value_1', 'Row_1'), ('Value_2', 'Row_1'), ('Value_3', 'Row_2'), ('Value_4', 'Row_3'), ('Value_5', 'Row_5')
推荐答案
您需要unpivot:
SELECT Id, Code, Other
FROM
Source
UNPIVOT
(Code FOR code_ IN
(code_1, code_2)
) AS unpvt;
请参见在此处进行小提琴.
这篇关于如何将表格的前两列堆叠为一列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
