matrix table into SQL

Posted on 2012-09-13
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 39

    Assisted Solution

    by:Kyle Abrahams
    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.
    LVL 6

    Accepted Solution

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now