deedub84
asked on
SQL Server Subquery in Select
Hi Experts,
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:
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.
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,
[varname]
,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.
Can you post some example data of before and after?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant! thanks Scott