storing an array of data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

storing an array of data

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2011-11-18 : 10:05:44

Hello all,

Need some help if anybody can, not looking for a working example more looking to be pushed in teh right direction as i'm not having my fun read and researching.

I'm tryin to store a array of data in * x * x * format, that can be query.

can this be done ?

Any help or links would be great ?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 10:13:44

You can be generic and difficyult to access
columns i,j,k,val where i,j,k is the cordinates of the element.

A table gives a *x* array
so cols represnt the i range
rows represent the j range
multiples of the rows represent the k range

It means there is a col for j and k and the i is the cols

j, k, i=1, i=2
1,1 a111, a211
2,1 a121, a221
1,2 a112, a212
2,2 a122, a222
...

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 10:25:43

quote:
Originally posted by lemondash

Hello all,

Need some help if anybody can, not looking for a working example more looking to be pushed in teh right direction as i'm not having my fun read and researching.

I'm tryin to store a array of data in * x * x * format, that can be query.

can this be done ?

Any help or links would be great ?

see

http://www.sommarskog.se/arrays-in-sql.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 11:54:40

You want a 3 dimensional array (i.e. every cell is defined by 3 values i,j,k) - tables are two dimensional so you just need to add the 3rd dimension.
You can do this by making multiple copies of the rows that make up the first two dimensions to make up the third.
For the 1st dimension w just need the columns and a single row.
For the 2nd we need to add rows - which need to be numbered (column i)
For the 3rd we add multiple copies of the 2 dimensional structure as extra rows. We also need another column to number the slices j.
The row for your 1 dimensional array will have i=1, j=1
For the 2 dimensional array j=1.
Note that a j entry will have the same number of i entries - columns so that is enforced.
A k entry will have the same number of i,j entries, i is enforced by columns but for j you ened to make sure the number of rows added is max(j) for each entry.

Of course another way of doing this would be xml - but I'm not saying that would be easier.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lemondash
Posting Yak Master

159 Posts

Posted - 2011-11-21 : 10:29:54

Sorry nigel only just saw this. But at the weekend i did come up with this. That creates me a 10 x 10 x 10 cube 0 - 999.

CREATE TABLE #MyTempTable
(
XAxis INT ,
YAxis INT ,
ZAxis INT
)

DECLARE @number1 INT
SET @number1 = 0

DECLARE @number2 INT
SET @number2 = 0

DECLARE @number3 INT
SET @number3 = 0

WHILE ( @number1 <= 9 )
BEGIN
WHILE ( @number2 <= 9 )
BEGIN
WHILE ( @number3 <= 9 )
BEGIN
INSERT INTO #MyTempTable
SELECT @number1 ,
@number2 ,
@number3

SELECT @number3 = @number3 + 1
END
SELECT @number3 = 0
SELECT @number2 = @number2 + 1
END
SELECT @number2 = 0
SELECT @number1 = @number1 + 1
END

SELECT XAxis ,
YAxis ,
ZAxis

FROM #MyTempTable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 11:36:25

no need of three loops. you can generate it in set based way also


;With CTE (N)
AS
(SELECT 0
UNION ALL
SELECT N +1
FROM CTE 
WHERE  N +1<=9
)

SELECT c1.N,c2.N,c3.N
FROM CTE c1
CROSS JOIN CTE c2
CROSS JOIN CTE c3
ORDER BY c1.N,c2.N,c3.N

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources