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.

What my original view looks like:

What my query outputs:

What I want the output to look like:

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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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)