How to return a single cell from a like matrix table?

Hi to all, I have a table that is in a form of "like" matrix

column names: name | grade class1 | grade class2 | grade class3 | ..... | grade n                     

Now I need to build a query that given the 'name'= "@given name" and the column name 'grade ..'="@give column" return the student name and his grade in the specific class , i dont want to do a query with the WHERE clause contains all the column names I tryed

(SELECT   @class,name                    
WHERE     data1$.[name] = @name                    

but it returns the name of the column and not the content of the cell but if I do

(SELECT   grade class1,name                    
WHERE     data1$.[name] = @name                    

I get the right output thanks in advance ad

more ▼

asked Nov 14, 2009 at 11:41 AM in Default

avatar image

2 4 4 5

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You need to "normalise" your data so that "Grade Class 1" and "Grade Class 2" etc. are not additional columns of the Student table, but are instead stored in a related table (lets call it [Class]

Then you can do:

SELECT *            
FROM Student            
     JOIN Class            
         ON Class.StudentID = Student.StudentID            
WHERE     Student.Name = @name            
      AND Class.Name = 'Class1'            
more ▼

answered Nov 14, 2009 at 04:19 PM

avatar image

Kristen ♦
2.2k 7 11 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 14, 2009 at 11:41 AM

Seen: 1277 times

Last Updated: Nov 14, 2009 at 04:16 PM

Copyright 2018 Redgate Software. Privacy Policy