问题描述
我问了一个很类似的问题 之前,但这次的要求有点复杂.我有一张如下所示的表格:
I asked a very similar question earlier, but the requirements this time are a bit more complex. I have a table that looks like the following:
| id | code_1 | code_2 | pair_1 |
|----|--------|--------|--------|
| 1 | a1 | a2 | b1 |
| 2 | a3 | a4 | (null) |
| 3 | a5 | (null) | b2 |
| 4 | a6 | (null) | (null) |
| 5 | (null) | a7 | b3 |
| 6 | (null) | a8 | (null) |
| 7 | (null) | (null) | b4 |
| 8 | (null) | (null) | (null) |
我想将 code_1 和 code_2 堆叠到一个列中,可以通过使用:
I want to stack code_1 and code_2 into a single column, which can be done by using:
select id, code, pair_1 as pair
from source
unpivot (code for code_ in (code_1, code_2)) as unpvt;
,产生以下输出:
| id | code | pair |
|----|------|--------|
| 1 | a1 | b1 |
| 1 | a2 | b1 |
| 2 | a3 | (null) |
| 2 | a4 | (null) |
| 3 | a5 | b2 |
| 4 | a6 | (null) |
| 5 | a7 | b3 |
| 6 | a8 | (null) |
,但我也想捕获 code_1
与 pair_1
配对的条件,即每当 code
来自 code_1
,它将使用pair_1
中的值作为pair
;并且每当 code
来自 code_2
时,它总是将 null
用于 pair
.
, but I also want to capture the condition that code_1
is paired with pair_1
i.e. whenever code
is sourced from code_1
, it will use the value in pair_1
for pair
; and whenever code
is sourced from code_2
, it will always use null
for pair
.
例如,给定原始表,目标表应如下所示:
So for example, given the original table, here is what the target table should look like:
| id | code | pair |
|----|------|--------|
| 1 | a1 | b1 |
| 2 | a2 | (null) |
| 3 | a3 | (null) |
| 4 | a4 | (null) |
| 5 | a5 | b2 |
| 6 | a6 | (null) |
| 7 | a7 | (null) |
| 8 | a8 | (null) |
DDL:
http://sqlfiddle.com/#!18/743a5/16
create table source (
id int identity(1, 1) primary key,
code_1 varchar(10),
code_2 varchar(10),
pair_1 varchar(10)
);
insert into source values
('a1', 'a2', 'b1'),
('a3', 'a4', null),
('a5', null, 'b2'),
('a6', null, null),
(null, 'a7', 'b3'),
(null, 'a8', null),
(null, null, 'b4'),
(null, null, null);
create table target (
id int identity(1, 1) primary key,
code varchar(10),
pair varchar(10)
);
insert into target values
('a1', 'b1'),
('a2', null),
('a3', null),
('a4', null),
('a5', 'b2'),
('a6', null),
('a7', null),
('a8', null);
推荐答案
试试下面的查询
SELECT * FROM
(
SELECT id, code_1 as code, pair_1 as pair FROM source WHERE code_1 IS NOT NULL
UNION ALL
SELECT id, code_2 as code, NULL as pair FROM source WHERE code_2 IS NOT NULL
) UnionTable
ORDER BY id
这篇关于如何将表格的前两列堆叠成一列,但也仅将第三列与第一列配对?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!