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 setup a monthly crosstab report with PIVOT?

Execute the following SQL Server T-SQL script in SSMS Query Editor to create a stored procedure for monthly sales results in columns for a particular year:

-- T-SQL create stored procedure with table variable

USE AdventureWorks;

GO

CREATE PROC MonthlyPurchaseOrder

           @Year INT

AS

  BEGIN

    DECLARE  @MonthlyPurchaseOrders  TABLE(

                                           VendorName NVARCHAR(50),

                                           OrderMonth INT,

                                           TotalDue   MONEY

                                           )

    

    INSERT @MonthlyPurchaseOrders

    SELECT v.Name,

           DATEPART(MONTH,OrderDate),

           TotalDue

    FROM   Purchasing.Vendor v

           JOIN Purchasing.PurchaseOrderHeader poh

             ON v.VendorID = poh.VendorID

    WHERE  v.Name LIKE ('[k-p]%')

           AND YEAR(OrderDate) = @Year

    

    SELECT *

    FROM   @MonthlyPurchaseOrders

           PIVOT

           (SUM(TotalDue)

            FOR OrderMonth IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] ) ) AS CrossTab

  END

GO

-- T-SQL execute stored procedure

EXEC MonthlyPurchaseOrder   2003

GO

 

VendorName 1 2 3 4 5 6 7 8 9 10 11 12
Lakewood Bicycle NULL NULL 6311.18 NULL NULL 6311.18 NULL NULL 25244.72 25244.72 6311.18 18933.54
Leaf River Terrain NULL NULL 383.9267 NULL NULL 625.5256 NULL NULL 4256.714 1977.379 474.4262 2121.274
Lindell NULL NULL 108.2513 NULL NULL 108.2513 NULL NULL 433.0052 433.0052 NULL 324.7539
Litware, Inc. NULL NULL NULL NULL 166.62 NULL 166.62 NULL 666.494 499.8705 166.6235 499.8705
Metro Sport Equipment NULL NULL 126.4905 NULL NULL 126.4905 NULL NULL 505.962 505.962 NULL 379.4715
Midwest Sport, Inc. NULL NULL 146.5744 NULL NULL 146.5744 NULL NULL 586.2976 586.2976 NULL 439.7232
Mitchell Sports NULL NULL 48485.69 NULL NULL 48485.69 NULL NULL 193942.7 145457.1 48485.69 145457.1
Morgan Bike Accessories NULL NULL 462.9398 NULL NULL 462.9398 NULL NULL 1851.759 1388.819 462.9398 1388.819
Mountain Works NULL NULL 430.5688 NULL NULL 265.9989 NULL NULL 1902.613 1452.378 4352.608 1306.778
National Bike Association NULL NULL 510.278 NULL NULL 510.278 NULL NULL 2041.112 1530.834 510.278 1530.834
Norstan Bike Hut NULL NULL 1085.611 NULL NULL 279.9219 NULL NULL 1673.44 2580.663 881.674 949.131
Northern Bike Travel NULL NULL 40.9684 NULL NULL 40.9684 NULL NULL 163.8736 122.9052 40.9684 122.9052
Premier Sport, Inc. NULL NULL NULL NULL 23822 23821.67 NULL NULL 95286.69 71465.02 23821.67 71465.02
Pro Sport Industries NULL NULL NULL NULL 761.21 287.8232 NULL NULL 2674.539 1474.759 466.4205 3407.898
Professional Athletic Consultants NULL NULL NULL NULL 23184 95273.93 NULL NULL 285821.8 140632.7 145189 285821.8
Proseware, Inc. NULL NULL NULL NULL 27995 NULL 36852 NULL 157690.2 129695.1 64847.53 129695.1

 

 

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