sql - 'Unpivoting' 一个 SQL 表

Question

我正在寻找“反透视”表，但我不确定最好的方法是什么。此外，这些值用“;”分隔。我列出了我正在查看的示例:
​​​


列_A
列_B
列_C
Column_D


000
A B C D
01;02;03;04
X;Y;D;E

001
甲;乙
05;06
英石

002
C
07
小号


​​​
从那开始，我正在寻找一种方法来取消它，但也要保持它当前所处的关系。如，Column_B、C 和 D 中的第一个值绑定(bind)在一起:
​​​
|Column_A|Column_B|Column_C|Column_D|
|:-|:-|:-|:-|
|000|A|01|X|
|000|B|02|Y|
|000|C|03|D|
|000|D|04|E|
|001|A|05|S|
等等。
我最初的想法是使用 CTE，我将其设置为:

WITH TEST AS(
SELECT DISTINCT Column_A, Column_B, Column_C, VALUE AS Column_D
from [TABLE]
CROSS APPLY STRING_SPLIT(Column_D, ';'))
SELECT \* FROM TEST
;

尽管这似乎不会产生正确的结果，尤其是在堆叠 CTE 和字符串拆分之后。

作为更新，下面有非常有用的解决方案。它们都按预期运行，但是我最后添加了一个。如果它是空白的，是否可以/合理地忽略行/列？例如，跳过 Column_C，其中 Column_A 为“001”。
|Column_A|Column_B|Column_C|Column_D|
|:-|:-|:-|:-|
|000|A;B;C;D|01;02;03;04|X;Y;D;E|
|001|A;B||S;T|
|002|C|07|S|

Answer 1

这是一个基于 JSON 的方法。 SQL Server 2016 及更高版本。
SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ColA varchar(3), ColB varchar(8000), ColC varchar(8000), ColD varchar(8000));
INSERT INTO @tbl VALUES
('000','A;B;C;D','01;02;03;04','X;Y;D;E'),
('001','A;B','05;06','S;T'),
('002','C','07','S');
-- DDL and sample data population, end

WITH rs AS 
(
     SELECT *
         , ar1 = '["' + REPLACE(ColB, ';', '","') + '"]'
         , ar2 = '["' + REPLACE(ColC, ';', '","') + '"]'
         , ar3 = '["' + REPLACE(ColD, ';', '","') + '"]'
     FROM @tbl
 )
 SELECT ColA, ColB.[value] AS [ColB], ColC.[value] AS ColC, ColD.[value] AS ColD
 FROM rs
    CROSS APPLY OPENJSON (ar1, N'$') AS ColB
    CROSS APPLY OPENJSON (ar2, N'$') AS ColC
    CROSS APPLY OPENJSON (ar3, N'$') AS ColD
 WHERE ColB.[key] = ColC.[key]
    AND ColB.[key] = ColD.[key];

输出

+------+------+------+------+
| ColA | ColB | ColC | ColD |
+------+------+------+------+
|  000 | A    |   01 | X    |
|  000 | B    |   02 | Y    |
|  000 | C    |   03 | D    |
|  000 | D    |   04 | E    |
|  001 | A    |   05 | S    |
|  001 | B    |   06 | T    |
|  002 | C    |   07 | S    |
+------+------+------+------+