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!

There is no case in you attempt? Show show us the attempt with the case. — Dale K
– Dale K, Commented Oct 18, 2021 at 23:26
I don't know where to start with putting a case in it. I tried creating a temp variable to hold a value that would then represent the code, but then I would have to find a way for it to read the comma delimited column anyway. Is that the best way to go about it? — Gabriella C.
– Gabriella C., Commented Oct 18, 2021 at 23:27
You can replace a column name with a case expression inside your VALUES statement. — Dale K
– Dale K, Commented Oct 18, 2021 at 23:34
And if you want to make it really easy for people to assist, provide the DDL+DML for your sample data. — Dale K
– Dale K, Commented Oct 18, 2021 at 23:35
What version of SQL Server? Also can you provide some sample data / desired results for overtime? And what happens if you have two John Does? Are you really using FullName to identify an employee? — Aaron Bertrand
– Aaron Bertrand, Commented Oct 19, 2021 at 1:31

Aaron Bertrand · Accepted Answer · 2021-10-19 01:45:12Z

2

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

answered Oct 19, 2021 at 1:45

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gabriella C. Over a year ago

This solution worked very elegantly, and did exactly what I wanted it to do. Thank you very much for your assistance!

Charlieface · Accepted Answer · 2021-10-19 00:44:12Z

1

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

answered Oct 19, 2021 at 0:44

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Comments

Venkataraman R · Accepted Answer · 2021-10-19 04:35:03Z

0

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

edited Oct 19, 2021 at 4:35

answered Oct 19, 2021 at 3:42

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

4 Comments

Aaron Bertrand Over a year ago

It's not quite the desired results (you have only used RegularTime but I think the OP wants the second row there to have the value from DoubleTime): dbfiddle.uk/…

Venkataraman R Over a year ago

@AaronBertrand, sorry. I have corrected the answer. Thanks for your check. Btw, I am fan of your SQL Server posts. I especially like your TSQL Bad habits to kick. Thanks for your service to SQL Server community.

Aaron Bertrand Over a year ago

Thanks! But Jane Doe should have 5, not 0, at least according to the question - I'm not quite sure that A or B means anything, it's just the position in the list that maps to which hours to pull. But I still think there might be something wrong with the sample data / desired results.

Venkataraman R Over a year ago

@AaronBertrand, yes. agree with you. there is confusion in the sample data and expected output.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related