DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to architect a yearly sales report?

Execute the following SQL Server T-SQL script in SSMS Query Editor to get the historic annual sales of all products with "Mountain" prefix:

------------
-- Microsoft SQL Server T-SQL PIVOT & CTE crosstab query
------------
-- SQL common table expression
-- MSSQL pivot for moving rows into column for cross tabulation
USE AdventureWorks;
WITH cteBikeSales(ProductName,SalesYear,TotalSales)
     AS (SELECT P.Name,
                Year(O.OrderDate),
                O.TotalDue
         FROM   Production.Product AS P
                INNER JOIN Sales.SalesOrderDetail AS OD
                  ON OD.ProductID = P.ProductID
                INNER JOIN Sales.SalesOrderHeader AS O
                  ON O.SalesOrderID = OD.SalesOrderID)
SELECT   ProductName,
         [2002] = COALESCE('$' + convert(VARCHAR,[2002],1),''),
         [2003] = COALESCE('$' + convert(VARCHAR,[2003],1),''),
         [2004] = COALESCE('$' + convert(VARCHAR,[2004],1),'')
FROM     cteBikeSales
         PIVOT
         (SUM(TotalSales)
          FOR SalesYear IN ( [2002],[2003],[2004] ) ) AS PVT
WHERE    left(ProductName,8) = 'Mountain'
ORDER BY ProductName
GO
/* Partial results
 
ProductName             2002              2003              2004
Mountain-200 Black, 38  $8,221,313.80     $14,490,482.30    $6,617,009.11
Mountain-200 Black, 42  $8,291,811.72     $13,917,073.94    $6,171,368.90
Mountain-200 Black, 46  $7,556,313.14     $13,821,273.81    $6,119,994.51
Mountain-200 Silver, 38 $7,793,200.86     $14,017,973.09    $6,426,702.59
Mountain-200 Silver, 42 $8,035,664.51     $13,620,125.41    $6,264,833.52
Mountain-200 Silver, 46 $8,244,978.42     $13,489,783.11    $5,813,895.77
Mountain-300 Black, 38  $7,186,088.55     $5,317,283.22    
Mountain-300 Black, 40  $7,479,713.53     $5,363,954.25    

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE