如何将逗号分隔的数据从一列拆分为多行

Question

我正在尝试编写一个查询，该查询将使一列根据另一个逗号分隔的列显示特定值。这些代码表示常规时间/加班时间/双倍时间等，它们来自前面提到的逗号分隔列。在原始视图中，每个不同的小时都有单独累积的列。为此，我们可以说A=正常时间，B=双倍时间，C=加班时间。然而，我们有许多代码可以表示相同类型的时间。

我最初的观点是什么：

Employee_FullName	EmpID
John Doe	123		Jane Doe	234

如果您使用的是SQL Server 2016或更高版本，您可以使用OPENJSON()来拆分代码值，而不是繁琐的字符串操作：

SELECT t.Employee_FullName,
Code = LTRIM(j.value),
Hours = MAX(CASE j.[key] 
WHEN 0 THEN RegularTime
WHEN 1 THEN DoubleTime
WHEN 2 THEN Overtime END)
FROM dbo.MyTable AS t
CROSS APPLY OPENJSON('["' + REPLACE(t.Code,',','","') + '"]') AS j
GROUP BY t.Employee_FullName, LTRIM(j.value);

• 示例db<gt；小提琴

您可以使用以下代码来拆分值

• 注意如果NULLIF返回0，它是如何将CHARINDEX清零的
• 第二个APPLY的后半部分以该null为条件

SELECT
t.[Employee_FullName],
Code = TRIM(v2.Code),
v2.Hours
FROM myTable t
CROSS APPLY (VALUES( NULLIF(CHARINDEX(',', t.Code), 0) )) v1(comma)
CROSS APPLY (
SELECT Code = ISNULL(LEFT(t.Code, v1.comma - 1), t.Code), Hours = t.RegularTime
UNION ALL
SELECT SUBSTRING(t.Code, v1.comma + 1, LEN(t.Code)), t.DoubleTime
WHERE v1.comma IS NOT NULL
) v2;

db<gt；小提琴

您可以采用下面给出的基于CROSS APPLY的方法。

感谢@Chalieface提供的插入脚本。

CREATE TABLE mytable (
"Employee_FullName" VARCHAR(8),
"Code" VARCHAR(3),
"RegularTime" INTEGER,
"DoubleTime" INTEGER,
"Overtime" INTEGER
);
INSERT INTO mytable
("Employee_FullName", "Code", "RegularTime", "DoubleTime", "Overtime")
VALUES
('John Doe', 'A,B', '10', '5', '0'),
('Jane Doe', 'B', '5', '0', '0');
SELECT
t.[Employee_FullName],
c.Code,
CASE WHEN c.code = 'A' THEN t.RegularTime
WHEN c.code = 'B' THEN t.DoubleTime
WHEN c.code = 'C' THEN t.Overtime
END AS Hours
FROM myTable t
CROSS APPLY (select value from string_split(t.code,',')
) c(code)

小时约翰·多伊

Employee_FullName	代码
John Doe	A	10
Jane Doe	B	0