[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL Server Subquery in Select

Posted on 2012-09-10
Medium Priority
Last Modified: 2012-09-10
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,
      ,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.
Question by:deedub84
LVL 15

Expert Comment

by:David L. Hansen
ID: 38384227
Can you post some example data of before and after?
LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 38384425
    Avg(CASE WHEN Isnumeric(M1.[varvalue])=1 THEN cast(M1.[varvalue] as float) Else 0 END) AS AvgValue
FROM (  
        imparcid, varvalue AS Instructor
    FROM [masterdata]
        imparcid=25 AND
        varname = 'S2Ins$'
) AS M2
INNER JOIN [masterdata] M1 ON
    M1.imparcid=25 AND
    M1.varname = 'S2GTS23A' AND

Open in new window


Author Closing Comment

ID: 38385330
Brilliant!  thanks Scott

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question