SQL query turn table around

Question

I have a sql query that selects data from several tables and do some calculations. The result it produces looks like this:

year    month   avg region
2011    1       123 UK
2012    1       0   UK
2013    1       0   UK
2011    2       400 UK
2012    2       200 UK
2013    2       0   UK

But I want the result to look like this:

month   year1   year2   year3   region
1       123     0       0       UK
2       400     200     0       UK

Here's my current query:

SELECT TOP (100) PERCENT DATEPART(YEAR, dbo.Fixtures.Date) AS YEAR,
                         DATEPART(MONTH, dbo.Fixtures.Date) AS MONTH,
                         AVG(dbo.Fixtures.PCRate) AS AveragePCRate,
                         dbo.Region.GroupName AS Region
FROM dbo.Fixtures
INNER JOIN dbo.Vessel ON dbo.Fixtures.VesselId = dbo.Vessel.ID
INNER JOIN dbo.Region ON dbo.Fixtures.RegionId = dbo.Region.ID
WHERE (YEAR(dbo.Fixtures.Date) >= YEAR(DATEADD(YEAR, - 2, GETDATE())))
  AND (dbo.Vessel.TypeId = 1)
  AND (dbo.Vessel.Total_BHP >= 15000)
  AND (dbo.Vessel.Total_BHP < 20000)
GROUP BY dbo.Region.GroupName,
         DATEPART(YEAR, dbo.Fixtures.Date),
         DATEPART(MONTH, dbo.Fixtures.Date)
ORDER BY YEAR, MONTH

Is this possible? P.S. There will only be data for the last 3 years.

Edit: This is what the result looks like with my SQL query.

year        month   avgPC   Region
2011    1   7300    Norway
2011    1   6818    United Kingdom
2011    2   8500    Norway
2011    2   6235    United Kingdom
2011    3   15400   Norway
2011    3   0       South America
2011    3   12545   United Kingdom
2011    4   22887   Norway
2011    4   17231   United Kingdom
2011    5   24033   Norway
2011    5   9730    United Kingdom
2011    6   24249   Norway
2011    6   25000   United Kingdom
2011    7   23310   Norway
2011    7   25495   United Kingdom
2011    8   35890   Norway
2011    8   23000   United Kingdom
2011    9   28055   Norway
2011    9   33510   United Kingdom
2011    10  66437   Norway
2011    10  53713   United Kingdom
2011    11  26300   Norway
2011    11  14264   United Kingdom
2011    12  10800   Norway
2011    12  14553   United Kingdom

And this is the result I get when executing the suggested piv query below:

m   year1   year2   year3   region
1   NULL    0   8500    Norway
2   NULL    NULL    NULL    Norway
3   NULL    NULL    NULL    Norway
4   NULL    NULL    NULL    Norway
5   NULL    NULL    NULL    Norway
6   NULL    NULL    NULL    Norway
7   NULL    NULL    NULL    Norway
8   NULL    NULL    NULL    Norway
9   NULL    NULL    NULL    Norway
10  NULL    NULL    NULL    Norway
11  NULL    NULL    NULL    Norway
12  NULL    NULL    NULL    Norway
3   NULL    NULL    NULL    South America
1   10250   6000    13000   United Kingdom
2   NULL    NULL    NULL    United Kingdom
3   NULL    NULL    NULL    United Kingdom
4   NULL    NULL    NULL    United Kingdom
5   NULL    NULL    NULL    United Kingdom
6   NULL    NULL    NULL    United Kingdom
7   NULL    NULL    NULL    United Kingdom
8   NULL    NULL    NULL    United Kingdom
9   NULL    NULL    NULL    United Kingdom
10  NULL    NULL    NULL    United Kingdom
11  NULL    NULL    NULL    United Kingdom
12  NULL    NULL    NULL    United Kingdom

Answer 1

Try:

SELECT 
    Month
    ,MAX(CASE WHEN YEAR = 2011 THEN AveragePCRate ELSE Null END) AS Year1
    ,MAX(CASE WHEN YEAR = 2012 THEN AveragePCRate ELSE Null END) AS Year2
    ,MAX(CASE WHEN YEAR = 2013 THEN AveragePCRate ELSE Null END) AS Year3
    ,Region 
FROM
(
SELECT TOP (100) PERCENT DATEPART(YEAR, dbo.Fixtures.Date) AS YEAR,
                         DATEPART(MONTH, dbo.Fixtures.Date) AS MONTH,
                         AVG(dbo.Fixtures.PCRate) AS AveragePCRate,
                         dbo.Region.GroupName AS Region
FROM dbo.Fixtures
INNER JOIN dbo.Vessel ON dbo.Fixtures.VesselId = dbo.Vessel.ID
INNER JOIN dbo.Region ON dbo.Fixtures.RegionId = dbo.Region.ID
WHERE (YEAR(dbo.Fixtures.Date) >= YEAR(DATEADD(YEAR, - 2, GETDATE())))
  AND (dbo.Vessel.TypeId = 1)
  AND (dbo.Vessel.Total_BHP >= 15000)
  AND (dbo.Vessel.Total_BHP < 20000)
GROUP BY dbo.Region.GroupName,
         DATEPART(YEAR, dbo.Fixtures.Date),
         DATEPART(MONTH, dbo.Fixtures.Date)
) YourQuery
GROUP BY Month, Region

You could make it fancier by making the CASE statements use the current date for its test (so that you only have three years at a time).

Answer 2

You did not specify what RDBMS you are using but since you are using TOP I am guessing SQL Server.

In SQL Server you can transform the data using the PIVOT function:

SELECT month,
  [2011] as year1, 
  [2012] as year2, 
  [2013] as year3,
  region
FROM
(
  SELECT DATEPART(year, dbo.Fixtures.Date) AS Year, 
    DATEPART(Month, dbo.Fixtures.Date) AS Month, 
    dbo.Fixtures.PCRate, 
    dbo.Region.GroupName AS Region
  FROM  dbo.Fixtures 
  INNER JOIN dbo.Vessel 
    ON dbo.Fixtures.VesselId = dbo.Vessel.ID 
  INNER JOIN dbo.Region 
    ON dbo.Fixtures.RegionId = dbo.Region.ID
  WHERE (YEAR(dbo.Fixtures.Date) >= YEAR(DATEADD(year, - 2, GETDATE()))) 
    AND (dbo.Vessel.TypeId = 1) 
    AND (dbo.Vessel.Total_BHP >= 15000) 
    AND (dbo.Vessel.Total_BHP < 20000)
) src
pivot
(
  avg(PCRate)
  for year in ([2011], [2012], [2013])
) piv;

If you do not want to hard-code the year values, then you can use something similar to the following:

SELECT month,
  year1, 
  year2, 
  year3,
  region
FROM
(
  SELECT 
    DATEPART(Month, dbo.Fixtures.Date) AS Month, 
    dbo.Fixtures.PCRate, 
    dbo.Region.GroupName AS Region,
    'year'+cast(row_number() over(partition by DATEPART(year, dbo.Fixtures.Date) 
                      order by DATEPART(year, dbo.Fixtures.Date)) as varchar(4)) year
  FROM  dbo.Fixtures 
  INNER JOIN dbo.Vessel 
    ON dbo.Fixtures.VesselId = dbo.Vessel.ID 
  INNER JOIN dbo.Region 
    ON dbo.Fixtures.RegionId = dbo.Region.ID
  WHERE (YEAR(dbo.Fixtures.Date) >= YEAR(DATEADD(year, - 2, GETDATE()))) 
    AND (dbo.Vessel.TypeId = 1) 
    AND (dbo.Vessel.Total_BHP >= 15000) 
    AND (dbo.Vessel.Total_BHP < 20000)
) src
pivot
(
  avg(PCRate)
  for year in (year1, year2, year3)
) piv;

Edit #1, It is difficult to tell from the data that you posted what the issue is, but if you cannot remove the avg from the subquery, then you should be able to use:

select month, [2011] as year1, [2012] as year2, [2013] as year3, region
from
(
  SELECT 
    DATEPART(YEAR, dbo.Fixtures.Date) AS YEAR,
    DATEPART(MONTH, dbo.Fixtures.Date) AS MONTH,
    AVG(dbo.Fixtures.PCRate) AS AveragePCRate,
    dbo.Region.GroupName AS Region
  FROM dbo.Fixtures
  INNER JOIN dbo.Vessel ON dbo.Fixtures.VesselId = dbo.Vessel.ID
  INNER JOIN dbo.Region ON dbo.Fixtures.RegionId = dbo.Region.ID
  WHERE (YEAR(dbo.Fixtures.Date) >= YEAR(DATEADD(YEAR, - 2, GETDATE())))
    AND (dbo.Vessel.TypeId = 1)
    AND (dbo.Vessel.Total_BHP >= 15000)
    AND (dbo.Vessel.Total_BHP < 20000)
  GROUP BY dbo.Region.GroupName,
           DATEPART(YEAR, dbo.Fixtures.Date),
           DATEPART(MONTH, dbo.Fixtures.Date)
) src
pivot
(
  max(AveragePCRate)
  for YEAR in ([2011], [2012], [2013])
) piv;