How to return all rows in a column when using cross apply and DelimitedSplit8K_LEAD together in SQL Server?

This code works to return the info that I need from the column but IT IS ONLY RETURNING ONE ROW and I need it to return them all.

You can leverage the DelimitedSplit8K_LEAD function which you can read more about here. https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2 You will also find the code for the function there.

select FirstValue = max(case when s.ItemNumber = 12 then s.Item end)
    , SecondValue = max(case when s.ItemNumber = 45 then try_convert(datetime, stuff(stuff(stuff(s.Item, 9, 0, ' '), 12, 0, ':'), 15, 0, '.')) end)
from myDatabase x 
cross apply DelimitedSplit8K_LEAD(x.myColumn, ',') s
where s.ItemNumber = 12
    or s.ItemNumber = 45

Here is an example of the data in the column that I'm trying to return.

,505611,XXXXXXX,,,,,,,,,13M2,,,,,,,,,,,03294961,,,,,,,,,,,,,,,,,,,,,XXXXX,20220216183348,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,US,,,0000,,,,,,,,,,,,,,,,,,,,

Here is an example of it working, just not with using it on the table column https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ca74e807853eb1dea445a8ffb7209b70

Okay so here is an example. Create a table in sql server database...

CREATE TABLE honda
(
user1 nvarchar(max)
);

INSERT INTO honda 
    (user1) 
VALUES 
(',523869,HXMFG-01,,,,,,,,,11M2,,,,,,,,,,,03311141,,,,,,,,,,,,,,,,,,,,,EAGLE,20220323082041,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,US,,,0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'),
(',523869,HXMFG-01,,,,,,,,,12M2,,,,,,,,,,,03311148,,,,,,,,,,,,,,,,,,,,,EAGLE,20220323093049,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,US,,,0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'),
(',523869,HXMFG-01,,,,,,,,,13M2,,,,,,,,,,,03311216,,,,,,,,,,,,,,,,,,,,,EAGLE,20220323100350,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,US,,,0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'),
(',523869,HXMFG-01,,,,,,,,,14M2,,,,,,,,,,,03311242,,,,,,,,,,,,,,,,,,,,,EAGLE,20220323103854,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,US,,,0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'),
(',523869,HXMFG-01,,,,,,,,,15M2,,,,,,,,,,,03311267,,,,,,,,,,,,,,,,,,,,,EAGLE,20220323112420,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,US,,,0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'),
(',527040,HXMFG-01,,,,,,,,,16M2,,,,,,,,,,,03311352,,,,,,,,,,,,,,,,,,,,,EAGLE,20220323122930,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,US,,,0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'),
(',527040,HXMFG-01,,,,,,,,,17M2,,,,,,,,,,,03311395,,,,,,,,,,,,,,,,,,,,,EAGLE,20220323130347,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,US,,,0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,');

If using sql server should only have to run this middle block of code once.

CREATE FUNCTION [dbo].[DelimitedSplit8K_LEAD]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table” produces values from 0 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT 0 UNION ALL
                 SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT t.N+1
                   FROM cteTally t
                  WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
        Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0)-s.N1,8000))
   FROM cteStart s
;
GO

If using dbfiddle take the GO off of the end in the above block.

select ECI_Level = max(case when s.ItemNumber = 12 then s.Item end)
    , 'DateTime' = max(case when s.ItemNumber = 45 then try_convert(datetime, stuff(stuff(stuff(s.Item, 9, 0, ' '), 12, 0, ':'), 15, 0, '.')) end)
from honda x 
cross apply DelimitedSplit8K_LEAD(x.user1, ',') s
where s.ItemNumber = 12
    or s.ItemNumber = 45

Solution

I was able to get it to work by adding GROUP BY like this...

select ECI_Level = max(case when s.ItemNumber = 12 then s.Item end)
    , 'DateTime' = max(case when s.ItemNumber = 45 then try_convert(datetime, stuff(stuff(stuff(s.Item, 9, 0, ' '), 12, 0, ':'), 15, 0, '.')) end)
from honda x 
cross apply DelimitedSplit8K_LEAD(x.user1, ',') s
where s.ItemNumber = 12
    or s.ItemNumber = 45
    GROUP BY user1;