You're right, that's a much better idea. To make it work for the OPs requirements, I'd stick it in a sproc to generate the result-set dynamically so that the number of "field" doesn't need to be known before-hand.

So first, we need Jeff's splitter: -

IF EXISTS (SELECT 1

FROM sysobjects

WHERE id = OBJECT_ID('DelimitedSplit8K'))

BEGIN

DROP FUNCTION [dbo].[DelimitedSplit8K];

END

GO

CREATE FUNCTION [dbo].[DelimitedSplit8K]

--===== Define I/O parameters

(@pString VARCHAR(8000), @pDelimiter CHAR(1))

--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

--===== "Inline" CTE Driven "Tally Table" produces values from 1 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 "base" CTE and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT TOP (ISNULL(DATALENGTH(@pString),0)) 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 1 UNION ALL

SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter

),

cteLen(N1,L1) AS(--==== Return start and length (for use in substring)

SELECT s.N1,

ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)

FROM cteStart s

)

--===== 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 l.N1),

Item = SUBSTRING(@pString, l.N1, l.L1)

FROM cteLen l

;

GO

Then we need the calling sproc: -

IF EXISTS (SELECT 1

FROM sysobjects

WHERE id = OBJECT_ID('SSC_Multi_Split'))

BEGIN

DROP PROCEDURE SSC_Multi_Split;

END

GO

CREATE PROCEDURE SSC_Multi_Split (@string VARCHAR(8000), @rowDeliminater CHAR(1), @colDeliminater CHAR(1))

AS

BEGIN

IF object_id('tempdb..#temporaryResultHolder') IS NOT NULL

BEGIN

DROP TABLE #temporaryResultHolder;

END;

SELECT ds1.ItemNumber AS rowNumber, ds2.ItemNumber AS columnNumber, ds2.Item AS data

INTO #temporaryResultHolder

FROM dbo.DelimitedSplit8K(@string, @rowDeliminater) ds1

CROSS APPLY dbo.DelimitedSplit8K(ds1.Item, @colDeliminater) ds2

WHERE ds1.Item <> '';

DECLARE @SQL NVARCHAR(MAX);

WITH CTE1(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),

CTE2(N) AS (SELECT 1 FROM CTE1 X CROSS JOIN CTE1 Y),

CTE3(N) AS (SELECT 1 FROM CTE2 X CROSS JOIN CTE2 Y),

CTE4(N) AS (SELECT 1 FROM CTE3 X CROSS JOIN CTE3 Y),

CTE5(N) AS (SELECT 1 FROM CTE4 X CROSS JOIN CTE4 Y),

CTE6(N) AS (SELECT 1 FROM CTE5 X CROSS JOIN CTE5 Y),

TALLY(N) AS (SELECT TOP (ISNULL(DATALENGTH(@string),0))

ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM CTE6)

SELECT @SQL = STUFF((SELECT ','+CHAR(13)+CHAR(10)+'MAX(CASE WHEN columnNumber = '+CAST(N AS VARCHAR(20))+' THEN data ELSE '+CHAR(39)+CHAR(39)+' END) AS field'+CAST(N AS VARCHAR(20))

FROM TALLY

CROSS APPLY (SELECT TOP 1 columnNumber

FROM #temporaryResultHolder

ORDER BY columnNumber DESC) b

WHERE columnNumber >= N

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)'),1,3,'');

SELECT @SQL = 'SELECT '+@SQL+CHAR(13)+CHAR(10)+'FROM #temporaryResultHolder'+CHAR(13)+CHAR(10)+'GROUP BY rowNumber'+CHAR(13)+CHAR(10)+'ORDER BY rowNumber';

EXECUTE sp_executeSQL @SQL;

END

GO

And we execute as follows: -

EXECUTE SSC_Multi_Split '30;38469|31;38470|32;38471|33;38472|34;38473|35;38474|36;38475|37;38476|38;38477|','|',';';