Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


matrix table into SQL

Posted on 2012-09-13
Medium Priority
Last Modified: 2012-09-13

I have a spreadsheet with a matrix style table of data, and just wondering how best to store and maintain in SQL.

here is a sample of the data:

                MNYC      MLON      RLON
MNYC       65        220        300
MLON       220        45         180
RLON       300        180        200
UK         300        200        200

so I have a list of areas (MNYC, MLON, RLON) and for each combination a price.

I would query the table like this:

price = start point to end point (eg 220=MLON to MNYC)

it doesn't matter which way you use the lookup (MNYC to MLON will be the same as MLON to MNYC)

thanks for any advice.
Question by:Graham_Forbes
LVL 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 1000 total points
ID: 38394921
4 columns
ID    From      To      Price

You really don't need the ID column but on general principal I like to keep a row ID on all of my tables whether it's used or not.

Accepted Solution

Peter Kiprop earned 1000 total points
ID: 38395043
Hi Graham_Forbes,

The ged32s answer above is correct. You may need to set From and To column as primary keys to avoid duplication of records.

Also ensure naming of areas is standardised.


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question