Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for July, 2012

Array to table for multi dimensional

Posted by Simon Cho on 07/18/2012

<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)

Advertisements

Posted in Common | Tagged: , , , | Leave a Comment »

SSMS tool pack and other useful free tools

Posted by Simon Cho on 07/11/2012

http://www.ssmstoolspack.com/

http://www.sqlserverutilities.com/

Posted in Common | Tagged: , , | Leave a Comment »