問題描述

我問了一個很類似的問題 之前，但這次的要求有點復雜.我有一張如下所示的表格:

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;

，產(chǎn)生以下輸出:

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

推薦答案

你不能做簡單的UNION ALL嗎?

試試下面的查詢

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

這篇關于如何將表格的前兩列堆疊成一列，但也僅將第三列與第一列配對?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！