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

Question

question

ami asked Nov 14, '09

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

sql-server-2008 normalization

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-11-14T16:19:04Z

Kristen answered Nov 14, '09

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'

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question