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-

  • Create the Provider db tables after RecreateDatabaseIfModelChanges
  • Access to Result sets from within Stored procedures Transact-SQL SQL Server
  • Check if SQL Server Client Is Installed
  • What SqlDbType maps to varChar(max)?
  • Runtime error in script task
  • Unable to connect to SQL Server with PHP
  • 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 to determine the number of days in a month in SQL Server?
  • ODBC continually prompts for password
  • SQL Server replace, remove all after certain character
  • SQL SELECT multi-columns INTO multi-variable
  • SQL Inline or Scalar Function?
  • SQL Server Table Lock during bulk insert
  • 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.