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
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'
