SQL corresponding column

Question

This is the output I got from my query. You can see Monthly sales for 1997 is followed by monthly sales of 1998. (It might not show in proper format here but they are all in a row)

Month   Year   Sales
---------------------------
1       1997   61258.07045
2       1997   38483.63504
3       1997   38547.21998
4       1997   53032.95254
5       1997   53781.28987
6       1997   36362.80255
7       1997   51020.85756
8       1997   47287.67004
9       1997   55629.24256
10      1997   66749.22589
11      1997   43533.80906
12      1997   71398.42874
1       1998   94222.11064
2       1998   99415.28734
3       1998   104854.155
4       1998   123798.6822
5       1998   18333.6304
6       1998   23245.34
7       1998   553894.34
8       1998   67004.67
9       1998   51020.85756
10      1998   38547.21998
11      1998   61258.07045
12      1998   53032.95254

How can get 1998 along the 1997 sales. Like-

Month | Sales1997 | Sales1998 
--------------------------------

The query I have till now -

Select T1.Mth, T1.Yr, T1.Sales 
 from  (Select month (o.OrderDate) Mth, 
               Year(o.orderdate)  Yr, 
               Sum((od.unitprice*od.Quantity)- (od.unitprice*od.Quantity*od.discount)) as Sales 
          from [Order Details] od 
          join Orders o on o.OrderID = od.OrderID 
 Group by month (o.OrderDate), Year(o.orderdate))  as T1 
   Where T1.Yr=1997 
      or T1.Yr=1998

Answer 1

Assuming SQL Server 2005+, using a CTE:

WITH summary AS (
      SELECT MONTH(o.OrderDate) AS Mth, 
             YEAR(o.orderdate) AS Yr, 
             SUM((od.unitprice * od.Quantity) - (od.unitprice*od.Quantity*od.discount)) as Sales
        FROM [Order Details] od 
        JOIN ORDERS o on o.OrderID = od.OrderID 
       WHERE YEAR(o.orderdate) IN (1997, 1998)
    GROUP BY MONTH(o.OrderDate), YEAR(o.orderdate))
  SELECT s.mth,
         MAX(CASE WHEN s.yr = 1997 THEN s.sales ELSE NULL END) AS sales1997,
         MAX(CASE WHEN s.yr = 1998 THEN s.sales ELSE NULL END) AS sales1998
    FROM summary s
GROUP BY s.mth

SQL Server 2005+ also provides PIVOT/UNPIVOT.

Without the CTE:

  SELECT s.mth,
         MAX(CASE WHEN s.yr = 1997 THEN s.sales ELSE NULL END) AS sales1997,
         MAX(CASE WHEN s.yr = 1998 THEN s.sales ELSE NULL END) AS sales1998
    FROM (SELECT MONTH(o.OrderDate) AS Mth, 
                 YEAR(o.orderdate) AS Yr, 
                 SUM((od.unitprice * od.Quantity) - (od.unitprice*od.Quantity*od.discount)) as Sales
            FROM [Order Details] od 
            JOIN ORDERS o on o.OrderID = od.OrderID 
           WHERE YEAR(o.orderdate) IN (1997, 1998)
        GROUP BY MONTH(o.OrderDate), YEAR(o.orderdate)) s
GROUP BY s.mth