sql - 使用 string_split 从多列创建行

Question

我的数据看起来像这个例子(不幸的是，规模要大得多):

+----+-------+--------------------+-----------------------------------------------+
| ID | Data  | Cost               | Comments                                      |
+----+-------+--------------------+-----------------------------------------------+
| 1  | 1|2|3 | $0.00|$3.17|$42.42 | test test||previous thing has a blank comment |
+----+-------+--------------------+-----------------------------------------------+
| 2  | 1     | $420.69            | test                                          |
+----+-------+--------------------+-----------------------------------------------+
| 3  | 1|2   | $3.50|$4.20        | |test                                         |
+----+-------+--------------------+-----------------------------------------------+

我所拥有的表中的某些列是管道分隔的，但它们每一行都是一致的。因此，每个分隔值对应于同一行其他列中的相同索引。

所以我可以做这样的事情，这就是我想要的单列:

SELECT ID, s.value AS datavalue
FROM MyTable t CROSS APPLY STRING_SPLIT(t.Data, '|') s

这会给我这个:

+----+-----------+
| ID | datavalue |
+----+-----------+
| 1  | 1         |
+----+-----------+
| 1  | 2         |
+----+-----------+
| 1  | 3         |
+----+-----------+
| 2  | 1         |
+----+-----------+
| 3  | 1         |
+----+-----------+
| 3  | 2         |
+----+-----------+

但我也想获得其他列(本例中的成本和评论)，以便相应的项目都在同一行中，如下所示:

+----+-----------+-----------+------------------------------------+
| ID | datavalue | costvalue | commentvalue                       |
+----+-----------+-----------+------------------------------------+
| 1  | 1         | $0.00     | test test                          |
+----+-----------+-----------+------------------------------------+
| 1  | 2         | $3.17     |                                    |
+----+-----------+-----------+------------------------------------+
| 1  | 3         | $42.42    | previous thing has a blank comment |
+----+-----------+-----------+------------------------------------+
| 2  | 1         | $420.69   | test                               |
+----+-----------+-----------+------------------------------------+
| 3  | 1         | $3.50     |                                    |
+----+-----------+-----------+------------------------------------+
| 3  | 2         | $4.20     | test                               |
+----+-----------+-----------+------------------------------------+

我不确定实现这一目标的最佳或最简单的方法是什么

Answer 1

这将无法通过 STRING_SPLIT 实现因为 Microsoft 拒绝提供序数位置作为结果集的一部分。因此，您需要使用不同的功能。我个人推荐 Jeff Moden 的 DelimitedSplit8k .

然后，你可以这样做:

CREATE TABLE #Sample (ID int,
                      [Data] varchar(200),
                      Cost  varchar(200),
                      Comments varchar(8000));
GO
INSERT INTO #Sample
VALUES (1,'1|2|3','$0.00|$3.17|$42.42','test test||previous thing has a blank comment'),
       (2,'1','$420.69','test'),
       (3,'1|2','$3.50|$4.20','|test');

GO
SELECT S.ID,
       DSd.Item AS DataValue,
       DSc.Item AS CostValue,
       DSct.Item AS CommentValue
FROM #Sample S
     CROSS APPLY dbo.DelimitedSplit8K(S.[Data],'|')  DSd
     CROSS APPLY (SELECT *
                  FROM DelimitedSplit8K(S.Cost,'|') SS
                  WHERE SS.ItemNumber = DSd.ItemNumber) DSc
     CROSS APPLY (SELECT *
                  FROM DelimitedSplit8K(S.Comments,'|') SS
                  WHERE SS.ItemNumber = DSd.ItemNumber) DSct;

GO
DROP TABLE #Sample;
GO

然而，这个问题只有一个真正的答案: 不要在 SQL Server 中存储分隔值。 以规范化的方式存储它们，你就不会有这个问题。