T-SQL - How to split (char separated) string into rows and columns

What is the best way to split a char separated string into rows and columns?

Here is my input string:

!1;100;10;200;0;500;2;1000;30!2;100;3;500;1;2000;5

And here is my desired output table:

Id  Value  Count
1   100    10
1   200    0
1   500    2
1   1000   30
2   100    3
2   500    1
2   2000   5

Best Regards,

It is not neccesary to modify Jeff Moden's function. I have devised a little bit sophisticated way of achieving the result just using the original function.

First Here it is the original Moden's function:
(Source: http://www.sqlservercentral.com/articles/Tally+Table/72993/)

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
;

And the query for obtaining the required result (I have created a CTE just for clarity)

DECLARE @string VARCHAR(8000)
SET @string = '!1;100;10;200;0;500;2;1000;30!2;100;3;500;1;2000;5'

;WITH CTE AS
(
SELECT 
SUBSTRING(A.Item,1,CHARINDEX(';',A.Item)-1) As Id, 
B.Item
FROM dbo.DelimitedSplit8K(@string, '!') AS A
CROSS APPLY dbo.DelimitedSplit8K(A.Item, ';') AS B
WHERE A.Item <> '' --to avoid blank first row because the first !
)

SELECT Id, [Value], [Count]
FROM
(
SELECT Id, Item As [Value], 
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id) As RowNumber,
LEAD(Item,1,0) OVER(PARTITION BY Id ORDER BY Id) As [Count]
FROM CTE
) As T
WHERE RowNumber % 2 = 0

EDITED:
I realized that my solution assummed that id value was ordered or that the order in the original string was not neccesary to be preserved in the result. Below the general solution (note that I changed the first number in the string for testing the situation)

DECLARE @string VARCHAR(8000)
SET @string = '!3;100;10;200;0;500;2;1000;30!2;100;3;500;1;2000;5'
;WITH CTE AS
(
SELECT 
ROW_NUMBER() OVER(ORDER BY (SELECT 0)) As RN,
CAST(SUBSTRING(A.Item,1,CHARINDEX(';',A.Item)-1) AS Varchar(400)) As Id, 
B.Item
FROM dbo.DelimitedSplit8K(@string, '!') AS A
CROSS APPLY dbo.DelimitedSplit8K(A.Item, ';') AS B
WHERE A.Item <> '' --to avoid blank first row because the first !
)

SELECT Id, [Value], [Count]
FROM
(
SELECT Id, Item As [Value], 
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id) As RowNumber,
LEAD(Item,1,0) OVER(PARTITION BY Id ORDER BY Id) As [Count]
,FIRST_VALUE(RN) OVER(PARTITION BY Id ORDER BY Id) AS FV
FROM CTE
) As T
WHERE RowNumber % 2 = 0
ORDER BY FV

http://sqlfiddle.com/#!6/31ccd/3

StackExchange Database Administrators Q#103600, answer score: 2