使用string_split从多列生成多行数据的技术咨询

Got it, let's tackle this problem of splitting those pipe-separated columns into matching rows using string_split! The core challenge is making sure each split value from Data, Cost, and Comments lines up correctly based on their position in the original string.

Step 1: Simulate Your Data (for Testing) #

First, let's create a temporary table to mirror your sample structure:

CREATE TABLE #TempData (
    ID INT,
    Data VARCHAR(100),
    Cost VARCHAR(100),
    Comments VARCHAR(200)
);

INSERT INTO #TempData VALUES
(1, '1|2|3', '$0.00|$3.17|$42.42', 'test test||previous thing has a blank comment'),
(2, '1', '$420.69', 'test');

Step 2: The Query to Split and Correlate Rows #

Use CROSS APPLY with string_split (with ordinal support, available in SQL Server 2022+) to split each column, then match ordinal positions to keep values aligned:

SELECT 
    td.ID,
    ds.Value AS DataValue,
    cs.Value AS CostValue,
    cmts.Value AS CommentValue
FROM #TempData td
CROSS APPLY string_split(td.Data, '|', 1) ds
CROSS APPLY string_split(td.Cost, '|', 1) cs
CROSS APPLY string_split(td.Comments, '|', 1) cmts
WHERE ds.ordinal = cs.ordinal 
  AND cs.ordinal = cmts.ordinal;

How This Works #

• CROSS APPLY runs string_split for every row in your original table, generating a new row for each split value in each column.
• The third parameter 1 in string_split enables the ordinal output, which returns the position of each split value in the original string (1, 2, 3, etc.). This is critical to ensure values from Data, Cost, and Comments pair correctly.
• The WHERE clause filters results to only keep rows where ordinal positions match across all three columns—so the first split value from Data pairs with the first from Cost and Comments, and so on.

Edge Cases to Keep in Mind #

• If some rows have mismatched split counts (e.g., Data splits into 3 values but Cost only splits into 2), the WHERE clause will exclude unpaired values. If you need to retain these (with empty values for missing columns), swap CROSS APPLY for OUTER APPLY and use ISNULL() to replace NULLs with empty strings.
• For SQL Server versions before 2022, string_split doesn't support ordinal. In that case, you'll need a custom split function that returns position numbers, or a workaround using ROW_NUMBER().

内容的提问来源于stack exchange，提问作者abney317