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

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

ami
2 ● 4 ● 4 ● 5

(comments are locked)

1 answer:

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'

answered Nov 14, 2009 at 04:19 PM

Kristen ♦
2.2k ● 7 ● 11 ● 14