SQL Server Subquery in Select

Posted on 2012-09-10
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
    Can you post some example data of before and after?
    LVL 68

    Accepted Solution

        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

    LVL 1

    Author Closing Comment

    Brilliant!  thanks Scott

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    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 …
    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now