In SQL server 2008 I have a table:
imparcid (PK, int, not null)
row (PK, int, not null0
varname (PK, nvarchar(50), not null)
vartype (nvarchar(50), null)
varvalue (nvarchar(max), null)
created from a 2 dimensional grid with varnames across the top and row down the side.
I am looking to create a SQL query that summarizes the data within a particular imparcid for one of the columns (i.e varname) but grouped by the values in another column. Imagine the columns are ratings on a number of scales for various teachers by a number of students. I can give more real-world background if needed.
I am using a subquery in my select statement as follows:
SELECT (Select Distinct varvalue FROM [masterdata] as M2 WHERE m2.imparcid = m1.imparcid AND m2.row = m1.row AND m2.varname = 'S2Ins$') as Instructor,
,Avg(CASE WHEN Isnumeric([varvalue])=1 THEN cast([varvalue] as float) Else 0 END)
FROM [masterdata] as M1
Group by Instructor, M1.row, M1.varname, M1.imparcid, M1.vartype
Having M1.imparcid=25 AND M1.varname = 'S2GTS23A'
What I'm having trouble with is how to create the group by clause appropriately so the result set has a row for each instructor, and the average value of the particular varname.
Not sure how to accomplish this.