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 build crosstab report by vendor by month using PIVOT?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to create a crosstab report on Purchase Orders by the application of the PIVOT operator and Total Sales crosstab by using the CASE function.

-- SQL pivot rows into column - sql pivot crosstab – crosstab query sql server

-- MSSQL create stored procedure - create stored procedure microsoft sql server

USE AdventureWorks2008;

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.BusinessEntityID = poh.VendorID

-- SQL wildcard selection

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

           AND YEAR(OrderDate) = @Year

    

    SELECT VendorName,

           Jan = '$' + convert(VARCHAR,[1],1),

           Feb = '$' + convert(VARCHAR,[2],1),

           Mar = '$' + convert(VARCHAR,[3],1),

           Apr = '$' + convert(VARCHAR,[4],1),

           May = '$' + convert(VARCHAR,[5],1),

           Jun = '$' + convert(VARCHAR,[6],1),

           Jul = '$' + convert(VARCHAR,[7],1),

           Aug = '$' + convert(VARCHAR,[8],1),

           Sep = '$' + convert(VARCHAR,[9],1),

           Oct = '$' + convert(VARCHAR,[10],1),

           Nov = '$' + convert(VARCHAR,[11],1),

           Dec = '$' + convert(VARCHAR,[12],1)

    FROM   @MonthlyPurchaseOrders

           PIVOT -- SQL Server pivot operator - pivot command sql server

           (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 - execute stored procedure sql server 2008

EXEC MonthlyPurchaseOrder  2004

GO

 

/*  Partial results

 

VendorName              Jan         Feb         Mar

Capital Road Cycles     $45,314.14  $60,418.86  $60,418.86

Carlson Specialties     $29,107.42  $54,343.56  $36,229.04

Chicago City Saddles    $137,282.52 $281,003.85 $293,824.03

Chicago Rent-All        $1,829.48   $2,439.31   $2,439.31

*/

------------

-- SQL Crosstab Query - CASE Function - sql server crosstab query

------------

-- Sales total crosstab by year by country - SUM aggregate function

USE AdventureWorks2008;

 

SELECT   Territory = LEFT(t.Name,20),

         '$' + convert(VARCHAR,SUM(CASE

                                     WHEN YEAR(OrderDate) = 2001 THEN TotalDue

                                     ELSE 0

                                   END),1) AS YY2001,

         '$' + convert(VARCHAR,SUM(CASE

                                     WHEN YEAR(OrderDate) = 2002 THEN TotalDue

                                     ELSE 0

                                   END),1) AS YY2002,

         '$' + convert(VARCHAR,SUM(CASE

                                     WHEN YEAR(OrderDate) = 2003 THEN TotalDue

                                     ELSE 0

                                   END),1) AS YY2003,

         '$' + convert(VARCHAR,SUM(CASE

                                     WHEN YEAR(OrderDate) = 2004 THEN TotalDue

                                     ELSE 0

                                   END),1) AS YY2004

FROM     Sales.SalesOrderHeader soh

         JOIN Sales.SalesTerritory t

           ON soh.TerritoryID = t.TerritoryID

GROUP BY LEFT(t.Name,20)

ORDER BY Territory

/*

Territory   YY2001            YY2002            YY2003            YY2004

Australia   $1,446,497.17     $2,380,484.84     $4,547,123.28     $3,823,410.24

Canada      $2,173,647.15     $7,215,430.50     $8,186,021.92     $3,926,712.89

Central     $1,263,884.10     $3,518,185.48     $4,015,356.87     $1,771,532.74

France      $199,531.72       $1,717,145.74     $4,366,078.35     $2,853,948.66

Germany     $262,752.42       $575,960.10       $2,714,826.43     $2,386,224.55

Northeast   $754,833.20       $3,275,322.17     $3,833,030.25     $1,406,555.69

Northwest   $2,703,481.79     $5,651,688.67     $7,494,658.04     $4,952,772.28

Southeast   $1,928,148.61     $3,814,944.14     $3,261,403.00     $1,566,084.67

Southwest   $3,272,568.52     $9,947,538.71     $11,523,237.52    $6,470,114.83

United Kingdom    $322,207.53 $1,778,804.75     $4,365,879.44     $3,039,555.88

*/

------------ 

 

 

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