Link to home
Start Free TrialLog in
Avatar of deedub84
deedub84Flag for United States of America

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

Open in new window


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.
Avatar of David L. Hansen
David L. Hansen
Flag of United States of America image

Can you post some example data of before and after?
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of deedub84

ASKER

Brilliant!  thanks Scott