T-SQL puzzler - given a cell in an N-dimensional s

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

SQL Server 2008 Forums

Transact-SQL (2008)

T-SQL puzzler - given a cell in an N-dimensional s

Author

Topic

lneville
Starting Member

12 Posts

Posted - 2013-11-14 : 11:21:04

The cells in an N-dimensional space are modelled with the 2 tables below. A script is needed that takes a single cell (by CellID) and returns all the other cells that are "inline" with the given cell along each axis (including itself).

For example, suppose the space has 3 dimensions (X, Y, Z) and X has 2 positions, Y has 2 and Z has 3. If the cell with coordinates {1,1,1} is given, the result should be:

+----------+---------+
| AxisCode | Cell |
+----------+---------+
| X | {1,1,1} | <- showing coordinates for clarity, but should be CellID
| X | {2,1,1} |
| Y | {1,1,1} |
| Y | {1,2,1} |
| Z | {1,1,1} |
| Z | {1,1,2} |
| Z | {1,1,3} |
+----------+---------+

I have spent hours on this and only come up with queries that are hard-coded for a specific number of dimensions ....

Please note:
**Changing the schema of the 2 tables is not an option!
The script has to work for N dimensions, and should not involve loops or cursors.**

Compatibility must be MS SQL 2008 R2

Any ideas gratefully received!

create table dbo.Cells(
CellID int not null,
CellValue int not null,
constraint PK_Cells primary key (CellID)
)

create table dbo.AxisPositions(
AxisCode char(1) not null, -- X, Y, Z etc
PositionOnAxis int not null, -- 1, 2, 3, 4 etc
constraint PK_AxisPositions primary key (AxisCode, PositionOnAxis)
)

create table dbo.CellAxes(
CellID int not null,
AxisCode char(1) not null, -- X, Y, Z etc
PositionOnAxis int not null, -- 1, 2, 3, 4 etc
constraint PK_CellAxes primary key (CellID, AxisCode),
constraint FK_CellAxes_Cells foreign key (CellID) references Cells(CellID),
constraint FK_CellAxes_AxisPositions foreign key (AxisCode, PositionOnAxis) references AxisPositions(AxisCode, PositionOnAxis)
)

-- Example data

insert Cells (CellID, CellValue)
values (1, 67), (2, 45), (3, 0), (4, 4), (5, 78), (6, 213), (7, 546), (8, 455), (9, 12), (10, 67), (11, 4), (12, 5)

insert AxisPositions (AxisCode, PositionOnAxis)
values ('X', 1), ('X', 2), ('Y', 1), ('Y', 2), ('Z', 1), ('Z', 2), ('Z', 3)

insert CellAxes (CellID, AxisCode, PositionOnAxis)
values (1, 'X', 1), (1, 'Y', 1), (1, 'Z', 1),
(2, 'X', 2), (2, 'Y', 1), (2, 'Z', 1),
(3, 'X', 1), (3, 'Y', 2), (3, 'Z', 1),
(4, 'X', 2), (4, 'Y', 2), (4, 'Z', 1),
(5, 'X', 1), (5, 'Y', 1), (5, 'Z', 2),
(6, 'X', 2), (6, 'Y', 1), (6, 'Z', 2),
(7, 'X', 1), (7, 'Y', 2), (7, 'Z', 2),
(8, 'X', 2), (8, 'Y', 2), (8, 'Z', 2),
(9, 'X', 1), (9, 'Y', 1), (9, 'Z', 3),
(10, 'X', 2), (10, 'Y', 1), (10, 'Z', 3),
(11, 'X', 1), (11, 'Y', 2), (11, 'Z', 3),
(12, 'X', 2), (12, 'Y', 2), (12, 'Z', 3)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-14 : 12:08:22

Can you post your desired results based on this sample data?

Be One with the Optimizer
TG

lneville
Starting Member

12 Posts

Posted - 2013-11-14 : 13:55:43

Results for sample data should be:

+----------+---------+
| AxisCode | CellID |
+----------+---------+
| X | 1 |
| X | 2 |
| Y | 1 |
| Y | 3 |
| Z | 1 |
| Z | 5 |
| Z | 9 |
+----------+---------+

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-14 : 15:22:18

I'm having trouble seeing how you get those results. Could you please post one of your hard coded (for dimension) versions so we can see the logic you are using?

Be One with the Optimizer
TG

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2013-11-14 : 16:37:44

quote:
Originally posted by TG

I'm having trouble seeing how you get those results. Could you please post one of your hard coded (for dimension) versions so we can see the logic you are using?

Be One with the Optimizer
TG

me too...


SELECT
    d.axisCode,
    d.cellID
FROM 
(
    SELECT 
        ca.*, 
        rank() over (partition by axiscode, positiononaxis order by cellid, positiononaxis) as coord
    FROM  dbo.cellAxes ca
) d
WHERE 
   (axiscode = 'X' and coord = 1)
OR (axiscode = 'Y' and coord = 1)
OR (axiscode = 'Z' and coord = 1) 
;

lneville
Starting Member

12 Posts

Posted - 2013-11-15 : 04:44:49

For posterity, a solution was worked out here: http://www.sqlservercentral.com/Forums/Topic1514358-392-1.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources