Simon's SQL

<Array to table for multi dimensional>

Many of people may be using this kind of logic to return arr to table using number table.

--Table "Num" is simply has sequence number.

DECLARE @ARR VARCHAR(MAX), @SEP varchar(max)
SET @ARR ='a,ab,abc,abcd,abcde'
SET @SEP =','

SELECT 
	NUM-LEN(REPLACE(LEFT(@ARR, NUM), @sep,'')) + 1 AS POS,
	SUBSTRING(@ARR, NUM, CHARINDEX(@sep,@ARR+@sep,NUM)-NUM) AS ELEMENT
  FROM num
WHERE NUM <= LEN(@ARR) AND SUBSTRING(@sep+@ARR, NUM,1)= @SEP;

But, this kind of logic can’t support multi dimensional array.
For example
a|1,ab|2,abc|3,abcd|4,abcde|5

CREATE FUNCTION [dbo].[func_split_multiDimensional]
(
@ss VARCHAR(max)
, @delimeter_row VARCHAR(10) = '|'
, @delimeter_col VARCHAR(10) = '-'
, @col_cnt int
)
RETURNS @COL_TMP TABLE (
		ROWNO INT
		, COL1 VARCHAR(max)
		, COL2 VARCHAR(max)
		, COL3 VARCHAR(max)
		, COL4 VARCHAR(max)
		, COL5 VARCHAR(max)
		, COL6 VARCHAR(max)
		, COL7 VARCHAR(max)
		, COL8 VARCHAR(max)
		, COL9 VARCHAR(max)
		, COL10 VARCHAR(max)
)
AS
-- =============================================
-- Author:		SIMON CHO
-- Create date: 2010.08.30
-- Description:	MultiDimensional array
-- =============================================
BEGIN

	DECLARE @X XML

	SET @SS = '<rt><r><c>'+@ss+'</c></r></rt>'
	SET @SS = REPLACE(@ss, @delimeter_row, '</c></r><r><c>')
	SET @SS = REPLACE(@ss, @delimeter_col, '</c><c>')

	SET @X = CAST(@SS AS XML)

	INSERT INTO @COL_TMP

	SELECT (ROWNO-1)/@col_cnt + 1 AS ROWNO
	,MAX((CASE (ROWNO-1)%@col_cnt WHEN 0 THEN COL END)) AS COL1
	,MAX((CASE (ROWNO-1)%@col_cnt WHEN 1 THEN COL END)) AS COL2
	,MAX((CASE (ROWNO-1)%@col_cnt WHEN 2 THEN COL END)) AS COL3
	,MAX((CASE (ROWNO-1)%@col_cnt WHEN 3 THEN COL END)) AS COL4
	,MAX((CASE (ROWNO-1)%@col_cnt WHEN 4 THEN COL END)) AS COL5
	,MAX((CASE (ROWNO-1)%@col_cnt WHEN 5 THEN COL END)) AS COL6
	,MAX((CASE (ROWNO-1)%@col_cnt WHEN 6 THEN COL END)) AS COL7
	,MAX((CASE (ROWNO-1)%@col_cnt WHEN 7 THEN COL END)) AS COL8
	,MAX((CASE (ROWNO-1)%@col_cnt WHEN 8 THEN COL END)) AS COL9
	,MAX((CASE (ROWNO-1)%@col_cnt WHEN 9 THEN COL END)) AS COL10
	FROM 
	(
		SELECT ROW_NUMBER() OVER(ORDER BY X.COL.value('@N', 'INT')) AS ROWNO
		     , X.COL.value('.', 'VARCHAR(100)') AS COL
		FROM @X.nodes('/rt/r/c') X(COL)
	) X
	GROUP BY (ROWNO-1)/@col_cnt
	ORDER BY (ROWNO-1)/@col_cnt

	RETURN
END

GO

SELECT * 
  FROM dbo.[func_split_multiDimensional]('a|1,ab|2,abc|3,abcd|4,abcde|5'
					,',','|',2)