<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.
–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
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)
Simon's SQL 