SQL corresponding column

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-

  • SQL Server Reporting Services export to another server
  • Remote SP Call via Linked Servers. Asynchronous or not?
  • Generate script in SQL Server 2005 with data
  • SQL: Update table where column = Multiple Values
  • How cross join two tables in SQL Server, repeating only some values
  • MERGE is inserting NULL records also
  • 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
    

  • How can I UNPIVOT columns into rows?
  • SQL, search for true, false or both
  • Use a specific database and table in MSSQL (Visual Studio)
  • append results of sql query
  • Fearing recursion in upon delete rows with foreign keys
  • SQL DataType Conversion Error
  • One Solution collect form web for “SQL corresponding column”

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.