Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2013
    Posts
    2

    Unanswered: How to create a matrix of values from a single table

    I'm a real novice user and I we use SQL Server. I have a table called TableLog that has the 4 columns. The first column is repeated x number of times for each value in the second column. I'd like to see this information put into a matrix where I could tell if there are any missing intersections. In addition, I need to insert a WHERE clause that says "BETWEEN 20090000 AND 20100000" for the first and second columns.

    Example data in the table:
    ID1 ID2 MRSSI
    100 200 63
    100 300 63
    200 100 63
    200 300 63
    300 100 63

    Desired matrix: P = Pass and F = Fail

    100 200 300
    100 n/a P P
    200 P n/a P
    300 P F P

    From the example data, there's no intersection for 300 and 200 so a F is displayed.

    Thx in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is this what you intended your table to look like?
    Code:
    	100	200	300
    100	n/a	P	P
    200	P	n/a	 P
    300	P	F	P
    Use the "code" tags to preserver white space.

    You can do this using a PIVOT table, or the SQL Pivot function.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2013
    Posts
    2
    I'll use tags in the future and yes, the layout is what I'm looking for. I've looked at examples of the Pivot function but I could use a example of how what the SQL should look like for my data.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by SickMEtal View Post
    I'm a real novice user and I we use SQL Server. I have a table called TableLog that has the 4 columns. The first column is repeated x number of times for each value in the second column. I'd like to see this information put into a matrix where I could tell if there are any missing intersections. In addition, I need to insert a WHERE clause that says "BETWEEN 20090000 AND 20100000" for the first and second columns.

    Example data in the table:
    ID1 ID2 MRSSI
    100 200 63
    100 300 63
    200 100 63
    200 300 63
    300 100 63

    Desired matrix: P = Pass and F = Fail

    100 200 300
    100 n/a P P
    200 P n/a P
    300 P F P

    From the example data, there's no intersection for 300 and 200 so a F is displayed.

    Thx in advance!
    Your requirements and descriptions were not clear and looking to include some inconsistencies for me.

    For example:
    (1) "I have a table called TableLog that has the 4 columns"
    But, you showed only 3 columns in your example data.

    (2) Wha were mean "P = Pass" and "F = Fail"?
    Do you want to say "P = Pass" means there was a value in the data?

    (3) What was the difference between "n/a" and "F" in your Desired matrix?
    Do you want "n/a" for same values in the final marix?

    (4) Why the result crossing 300 and 300 was P?
    Code:
          100   200   300
    100   n/a   P     P
    200   P     n/a   P
    300   P     F     P
    No data in your Example data corresponding to 300 and 300.
    So, isn't it "F" or "n/a"?
    (If answer for question 3 was "n/a", might it be "n/a"?)

    (5) "I need to insert a WHERE clause that says "BETWEEN 20090000 AND 20100000" for the first and second columns"
    Both columns were between 100 and 300.
    All data which you showed might be not satisfy the WHERE clause. Then no data might be selected in the result.
    What really do you want to say by that sentense?
    Last edited by tonkuma; 08-28-13 at 16:09.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •