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

more ▼

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

ami gravatar image

2 4 4 5

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

1 answer: sort newest

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:

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

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 1080 times

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