question

ami avatar image
ami asked

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-2008normalization
10 |1200

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

1 Answer

·
Kristen avatar image
Kristen answered

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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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