How to split comma delimited data from one column into multiple rows

Question

I'm trying to write a query that will have a column show a specific value depending on another comma delimited column. The codes are meant to denote Regular time/overtime/doubletime/ etc. and they come from the previously mentioned comma delimited column. In the original view, there are columns for each of the different hours accrued separately. For the purposes of this, we can say A = regular time, B = doubletime, C = overtime. However, we have many codes that can represent the same type of time.

What my original view looks like:

Employee_FullName	EmpID	Code	Regular Time	Double Time	Overtime
John Doe	123	A,B	7	2	0
Jane Doe	234	B	4	0	1

What my query outputs:

Employee_FullName	EmpID	Code	Hours
John Doe	123	A, B	10
John Doe	123	A, B	5
Jane Doe	234	B	5

What I want the output to look like:

Employee_FullName	EmpID	Code	Hours
John Doe	123	A	10
John Doe	123	B	5
Jane Doe	234	B	5

It looks the way it does in the first table because currently it's only pulling from the regular time column. I've tried using a case switch to have it look for a specific code and then pull the number, but I get a variety of errors no matter how I write it. Here's what my query looks like:

SELECT [Employee_FullName],
    SUBSTRING(col, 1, CHARINDEX(' ', col + ' ' ) -1)'Code',
    hrsValue
FROM
    (
    SELECT [Employee_FullName], col, hrsValue
    FROM myTable
    CROSS APPLY
    (
        VALUES ([Code],[RegularHours])
    ) C (COL, hrsValue)
) SRC

Any advice on how to fix it or perspective on what to use is appreciated!

Edit: I cannot change the comma delimited data, it is provided that way. I think a case within a cross apply will solve it but I honestly don't know.

Edit 2: I will be using a unique EmployeeID to identify them. In this case yes A is regular time, B is double time, C is overtime. The complication is that there are a variety of different codes and multiple refer to each type of time. There is never a case where A would refer to regular time for one employee and double time for another, etc. I am on SQL Server 2017. Thank you all for your time!

Answer 1

If you are on SQL Server 2016 or better, you can use OPENJSON() to split up the code values instead of cumbersome string operations:

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

• Example db<>fiddle

Answer 2

You can use the following code to split up the values

• Note how NULLIF nulls out the CHARINDEX if it returns 0
• The second half of the second APPLY is conditional on that 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<>fiddle

Answer 3

You can go for CROSS APPLY based approach as given below.

Thanks to @Chalieface for the insert script.

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	Code	Hours
John Doe	A	10
John Doe	B	5
Jane Doe	B	0