ok Dwain, I love these performance tests.

here's a setup comparing my crappy scalar function to your solution.

the DelimitedSplit8K pivot was roughly 4x faster on a million rows: 8 seconds vs 2 seconds, roughly.

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== BASELINE ==========

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 180 ms.

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== Scalar Function ==========

SQL Server Execution Times:

CPU time = 7971 ms, elapsed time = 8242 ms.

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== DelimitedSplit8K Featuring Pivot ==========

Warning: Null value is eliminated by an aggregate or other SET operation.

SQL Server Execution Times:

CPU time = 2200 ms, elapsed time = 2472 ms.

and here is my full test harness, but assuming you already have DelimitedSplit8K and my function from above already installed:

--===== Suppress the auto-display of rowcounts to keep them from being

--===== mistaken as part of the result set.

SET NOCOUNT ON

SET STATISTICS TIME OFF;

--=====Drop temp table if it exists for script repeatability

IF (SELECT OBJECT_ID('Tempdb.dbo.#SampleData')) IS NOT NULL

DROP TABLE #SampleData

--=====Generate a million rows of test data matching our string pattern.

SELECT TOP 1000000

ROW_NUMBER() over (order by T1.object_id) As DETAILS_ID,

REPLACE(CONVERT(varchar(40),newid()) ,'-','')

+ '|'

+ CONVERT(varchar(10),T1.create_date,120)

+ '|'

+ CONVERT(varchar(10),T1.modify_date,120)

+ '|'

+ CONVERT(varchar,T1.object_id)

+ '|'

+ 'PL'

+ CONVERT(VARCHAR,ABS(CHECKSUM(NEWID()))%50000+1)

AS DETAILS

INTO #SampleData

FROM sys.objects T1

CROSS JOIN sys.objects T2

DECLARE @HOLDER VARCHAR(20);

PRINT REPLICATE('-',80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== BASELINE ==========';

SET STATISTICS TIME ON;

SELECT @HOLDER = COUNT(*)

FROM #SampleData;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT REPLICATE('-',80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== Scalar Function ==========';

SET STATISTICS TIME ON;

SELECT

dbo.fn_parsename(DETAILS,'|',1) AS [DETAILS_ID],

dbo.fn_parsename(DETAILS,'|',2) AS [Column 1],

dbo.fn_parsename(DETAILS,'|',3) AS [Column 2],

dbo.fn_parsename(DETAILS,'|',4) AS [Column 3],

dbo.fn_parsename(DETAILS,'|',5) AS [Column 4]

FROM #SampleData;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT REPLICATE('-',80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== DelimitedSplit8K Featuring Pivot ==========';

SET STATISTICS TIME ON;

;WITH MySplit AS (

SELECT DETAILS_ID, DETAILS, ItemNumber

,Item = CASE WHEN ItemNumber <= 3 AND ItemNumber > 1 THEN CONVERT(VARCHAR(10), CAST(Item AS DATE), 101) ELSE Item END

FROM #SampleData

CROSS APPLY DelimitedSplit8K(DETAILS, '|'))

SELECT DETAILS_ID

,Col1=MAX(CASE WHEN ItemNumber = 1 THEN Item END)

,Col2=MAX(CASE WHEN ItemNumber = 2 THEN Item END)

,Col3=MAX(CASE WHEN ItemNumber = 3 THEN Item END)

,Col4=MAX(CASE WHEN ItemNumber = 5 THEN Item END)

FROM MySplit a

GROUP BY DETAILS_ID

SET STATISTICS TIME OFF;