SQL Server reference fields in derived table with unions

Question

I'm having a bit of an issue with some derived tables that I hope someone will be able to help with. What I've got is 2 derived tables inside a select statement that then uses a pivot to display the results horizontally rather than vertically.

What I've got so far is:

SELECT * FROM(
SELECT SUM(Value) AS TotDays, ClassId FROM MainTable GROUP BY ClassId)
Union All
SELECT SUM(NumDays) As TotDays, ClassId FROM (
SELECT CASE WHEN COUNT(SiteId) > 0 THEN 1 ELSE 0 END AS NumDays
  FROM Table2 GROUP BY ClassId ) as SUB
) AS a
PIVOT (SUM(TotDays) FROM ClassId
   IN ([12],[13],[14],[15]

What I'm trying to do is reference the individual columns rather than using SELECT *, but I don't know how to do it. I can make it work without if I drop everything from the union onwards, but when I put the union in it doesn't work and I have to use SELECT *.

Anyone got any ideas on what's going wrong?

Thanks

Alex

score 0 · Accepted Answer · answered Feb 18 '13 at 16:38

You have a couple of errors on your query. For example, your UNION ALL has sets with a different number of columns, and you have other syntax errors. Try this way:

SELECT  [12],[13],[14],[15]
FROM (  SELECT SUM(Value) AS TotDays, ClassId 
        FROM MainTable 
        GROUP BY ClassId
        UNION ALL
        SELECT SUM(NumDays) As TotDays, ClassId 
        FROM (  SELECT  CASE WHEN COUNT(SiteId) > 0 THEN 1 ELSE 0 END NumDays, 
                        ClassId
                FROM Table2 
                GROUP BY ClassId) as SUB
    ) AS a
PIVOT (SUM(TotDays) FROM ClassId IN ([12],[13],[14],[15])) AS PT

SQL Server reference fields in derived table with unions

1 Answers1