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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to architect a sales crosstab by city & state?

Execute the following T-SQL script in Microsoft SQL Server Management Studio Query Editor to demonstrate the usage of the CTE-s and PIVOT for complex crosstab reporting:

-- SQL common table expression - CTE

-- T-SQL pivot operator - crosstab query - pivot rows into columns

-- MSSQL - pivot PO count and PO sum of TotalDue - double pivot - multiple pivots

USE AdventureWorks;

GO

WITH cteVendorPO

     AS (SELECT PurchaseOrderID,

                City = City,

                State = StateProvinceCode,

                Employee = FirstName + ' ' + LastName,

                Vendor = v.Name

         FROM   Purchasing.PurchaseOrderHeader poh

                INNER JOIN HumanResources.Employee e

                  ON poh.EmployeeID = e.EmployeeID

                INNER JOIN Person.Contact c

                  ON e.ContactID = c.ContactID

                INNER JOIN Purchasing.Vendor v

                  ON v.VendorID = poh.VendorID

                INNER JOIN Purchasing.VendorAddress va

                  ON v.VendorID = va.VendorID

                INNER JOIN Person.Address a

                  ON a.AddressID = va.AddressID

                INNER JOIN Person.StateProvince sp

                  ON sp.StateProvinceID = a.StateProvinceID),

     cteVendorTotalDue

     AS (SELECT Employee = FirstName + ' ' + LastName,

                City = City,

                State = StateProvinceCode,

                Vendor = v.Name,

                TotalDue

         FROM   Purchasing.PurchaseOrderHeader poh

                INNER JOIN HumanResources.Employee e

                  ON poh.EmployeeID = e.EmployeeID

                INNER JOIN Person.Contact c

                  ON e.ContactID = c.ContactID

                INNER JOIN Purchasing.Vendor v

                  ON v.VendorID = poh.VendorID

                INNER JOIN Purchasing.VendorAddress va

                  ON v.VendorID = va.VendorID

                INNER JOIN Person.Address a

                  ON a.AddressID = va.AddressID

                INNER JOIN Person.StateProvince sp

                  ON sp.StateProvinceID = a.StateProvinceID)

SELECT   pvt1.State,

         pvt1.City,

         pvt1.Vendor,

         [Erin Hagens POs] = pvt1.[Erin Hagens],

         -- T-SQL money formatting - format currency

         [Erin Hagens Amount] = '$' + convert(VARCHAR,pvt2.[Erin Hagens],1),

         [Linda Meisner POs] = pvt1.[Linda Meisner],

         [Linda Meisner Amount] = '$' + convert(VARCHAR,pvt2.[Linda Meisner],1),

         [Sheela Word POs] = pvt1.[Sheela Word],

         [Sheela Word Amount] = '$' + convert(VARCHAR,pvt2.[Sheela Word],1),

         [Frank Pellow POs] = pvt1.[Frank Pellow],

         [Frank Pellow Amount] = '$' + convert(VARCHAR,pvt2.[Frank Pellow],1),

         [Reinout Hillmann POs] = pvt1.[Reinout Hillmann],

         [Reinout Hillmann Amount] = '$' + convert(VARCHAR,pvt2.[Reinout Hillmann],1)

FROM     cteVendorPO

         PIVOT

         (COUNT(PurchaseOrderID)

          FOR Employee IN ( [Erin Hagens],[Linda Meisner],[Sheela Word],

          [Frank Pellow],[Reinout Hillmann] ) ) AS pvt1

         INNER JOIN cteVendorTotalDue

              PIVOT

              (sum(totaldue)

               FOR Employee IN ( [Erin Hagens],[Linda Meisner],[Sheela Word],

               [Frank Pellow],[Reinout Hillmann] ) ) AS pvt2

           ON pvt1.Vendor = pvt2.Vendor

              AND pvt1.State = pvt2.State

              AND pvt1.City = pvt2.City

ORDER BY pvt1.State,

         pvt1.City,

         pvt1.Vendor

GO

/* Partial results

 

State City Vendor Erin Hagens POs Erin Hagens Amount Linda Meisner POs Linda Meisner Amount
AZ  Lemon Grove Greenwood Athletic Company 5 $242,428.44 5 $242,428.44
CA  Altadena Allenson Cycles 4 $39,105.07 5 $48,881.33
CA  Altadena Gardner Touring Cycles 5 $2,513.10 5 $2,513.10
CA  Berkeley Cruger Bike Company 4 $1,527.35 5 $2,603.57
CA  Berkeley Trikes, Inc. 4 $116,932.32 5 $146,165.39
CA  Burbank Anderson's Custom Bikes 5 $80,820.11 6 $96,984.14

*/

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

 


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