Split string to get tabular format

Transact-SQL
,
1

Need help to split following string into tabular format

DECLARE @StringVal VARCHAR(500)
SELECT @StringVal = '1,X,55,XX:2,Y,33,YY:3,Z,66,ZZ'

Output Required
COL1 | Col2 | Col3 | Col4

1 |X |55 |XX
2 |Y |33 |YY
3 |Z |66 |ZZ

2

Get a string splitter, for example the delimitedSplit8K described here. Then use it like in the example below

DECLARE @StringVal VARCHAR(500)
SELECT @StringVal = '1,X,55,XX:2,Y,33,YY:3,Z,66,ZZ'

SELECT [1] AS Col1, [2] AS Col2, [3] AS Col3, [4] AS Col4 FROM 
(
SELECT 
	a.ItemNumber AS an,
	b.itemnumber AS bn,
	b.Item
FROM 
	dbo.delimitedSplit8K(@StringVal,':') a
	CROSS APPLY
	(	SELECT * FROM  dbo.delimitedSplit8K(a.Item,',') ) b
)c
PIVOT
(
	MAX(Item)
	FOR bn IN ([1], [2], [3], [4])
)P
1 Like
3

Thanks James