Tabledesign

Question

Post reply

Tabledesign

Knallofall

SSC Journeyman

Points: 92
More actions
October 23, 2011 at 6:27 am

#245646

Hello,
I have a problem designing a table from a matrix in excel. Is it even possible in SQL Server 2005?
Look at the posted link for example. What I want is to be able to select the value 36 if my inparameter is 2636 (x -axis) and 200 (y -axis) from the example

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

just an idea :

USE [tempdb]

GO

CREATE TABLE [dbo].[TableXY](

[Y] [int] NOT NULL,

[XMIN] [int] NOT NULL,

[XMAX] [int] NOT NULL,

[RESULT] [int] NULL,

CONSTRAINT [PK_TableXY] PRIMARY KEY CLUSTERED

(

[Y] ASC,

[XMIN] ASC,

[XMAX] ASC

)

) ON [PRIMARY]

GO

INSERT INTO [dbo].[TableXY]([Y], [XMIN], [XMAX], [RESULT])

SELECT 50, 500, 999, 75 UNION ALL

SELECT 50, 1000, 2499, 50 UNION ALL

SELECT 100, 500, 999, 85 UNION ALL

SELECT 100, 1000, 2499, 55 UNION ALL

SELECT 200, 500, 999, 95 UNION ALL

SELECT 200, 1000, 2499, 70

SELECT Y,

XMIN,

XMAX,

RESULT

FROM TableXY

WHERE ( Y = 200 )

AND ( 1999 >= XMIN )

AND ( 1999 <= XMAX )

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Knallofall SSC Journeyman Points: 92 More actions · Answer 2

yes this should work but column y would be repetetive, but maybe that cant be avoided?

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

I feel like I've stepped into a parallel universe. The language sounds like English, but I don't understand it.

Can you please try again to explain what you are trying to do? Are you just trying to import data into SQL Server, or something more than that?

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

Phil Parkin (10/23/2011)
I feel like I've stepped into a parallel universe. The language sounds like English, but I don't understand it.
Can you please try again to explain what you are trying to do? Are you just trying to import data into SQL Server, or something more than that?

the OP has a spreadsheet that looks similar to this

500-999 | 1000-2499 |

50 75 | 50 |

100 85 ! 55 |

200 95 | 70 |

and based on the example I gave, wishes to return a value from the "matrix" where "x" value = 200 and "y" value fall between the ranges along the columns.

...of course I may well be in another parallel universe where my assumptions are totally skewed by the time/space differential...particluarly as I have had a few Pan Galactic Gargle Blasters.....:w00t:

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

Aha. Yes, I think you have it.

This would be a lot easier if the table could have a more-normalised design, eg:

TabXY(x, y_start, y_end, value)

then your queries would work very simply.