Array to table for multi dimensional

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
--Table "Num" is simply has a sequence number.

CREATE FUNCTION [dbo].[fn_Split](@str NVARCHAR(max), @splitValue VARCHAR(10)=',')

RETURNS TABLE

AS

RETURN

(

	SELECT N-LEN(REPLACE(LEFT(@str, N), @splitValue, ")) + 1 AS POS

		 , CONVERT(NVARCHAR(4000), SUBSTRING(@str, N, CHARINDEX(@splitValue, @str+@splitValue, N) -- N)) AS ELEMENT

	  FROM dbo.NUM

	 WHERE N <= LEN(@str) AND SUBSTRING(@splitValue+@str, N, LEN(@splitValue))= @splitValue

	   AND SUBSTRING(@str, N, CHARINDEX(@splitValue, @str+@splitValue, N) -- N)"

)

GO

select * from dbo.fn_Split(‘1,2,3,4’)

But, this kind of logic can’t support a multi-dimensional array.

Here is another way to implement it. For example a|1,ab|2,abc|3,abcd|4,abcde|5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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 = ‘’+@ss+’’ SET @SS = REPLACE(@ss, @delimeter_row, ‘’) SET @SS = REPLACE(@ss, @delimeter_col, ‘’)

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

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