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

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

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

Advertisement

Answer