SubQuery

[MattSmithProg]

Hi,

I have a problem trying to return data in a manner that is suitable.

I have a table which records the productivity of people by month. It looks something like this.

UserId Name Site Month Productivity
xxxx1 Fred City 1 0.85
xxxx2 Bob Country 1 1.12
xxxx1 Fred City 2 1.59
xxxx2 Bob Country 2 1.18
.
.
.
.

What I would like is to have the return values after an SQL call to look like this.

Average Productivity/Month
Site 1 2 3 4 5 6 7 8 ..
City 0.85 1.59
Country 1.12 1.18


I have tried

Select Month, Site, Avg(Productivity) From TableName Where Site = 'City' or Site = 'Country' Group By Site, Month Order By Month

I have a feeling that I need to use a Subquery or something similar but I am not sure how to do this as I am still new to SQL.

Could someone please help me.

Thanks

Matt

 

[tlbroadbent]

You can create a Crosstab query in T-SQL a follows.

Select Site,
sum(Case When [Month] = 1 Then AvgProd Else 0 End) As M1,
sum(Case When [Month] = 2 Then AvgProd Else 0 End) As M2,
sum(Case When [Month] = 3 Then AvgProd Else 0 End) As M3,
sum(Case When [Month] = 4 Then AvgProd Else 0 End) As M4,
sum(Case When [Month] = 5 Then AvgProd Else 0 End) As M5,
sum(Case When [Month] = 6 Then AvgProd Else 0 End) As M6,
sum(Case When [Month] = 7 Then AvgProd Else 0 End) As M7,
sum(Case When [Month] = 8 Then AvgProd Else 0 End) As M8,
sum(Case When [Month] = 9 Then AvgProd Else 0 End) As M9,
sum(Case When [Month] = 10 Then AvgProd Else 0 End) As M10,
sum(Case When [Month] = 11 Then AvgProd Else 0 End) As M11,
sum(Case When [Month] = 12 Then AvgProd Else 0 End) As M12
From (
Select site, mnth, avg(productivity) AvgProd
from TableName Where Site In ('City', 'Country')
Group By site, Mnth) As qry
Group By Site
Order By Site

I recommend that you not use "Month" as a column name. It is a reserved word in T-SQL. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.