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 construct a crosstab of subcategory by color?

Execute the following Microsoft SQL Server T-SQL script in Query Editor to create a cross tabulation of average list price for subcategories by colors.

The AVG, sql group by aggreage function, is applied for aggregation.

The CASE function is used to achieve of moving data from rows into columns (pivoting).

The COALESCE function replaces NULL with empty string ('').

USE AdventureWorks

GO

-- SQL crosstab case - SQL case crosstab - SQL group by aggregate

SELECT   sc.Name  AS Subcategory,

         COALESCE(CAST(AVG(CASE

               WHEN Color = 'Multi' THEN ListPrice

             END) AS varchar), '') AS Multi,

         COALESCE(CAST(AVG(CASE

               WHEN Color = 'Silver/Black' THEN ListPrice

             END) AS varchar), '') AS Silver_Black,

         COALESCE(CAST(AVG(CASE

               WHEN Color = 'Silver' THEN ListPrice

             END) AS varchar), '') AS Silver,

         COALESCE(CAST(AVG(CASE

               WHEN Color = 'Grey' THEN ListPrice

             END) AS varchar), '') AS Grey,

         COALESCE(CAST(AVG(CASE

               WHEN Color = 'White' THEN ListPrice

             END) AS varchar), '') AS White,

         COALESCE(CAST(AVG(CASE

               WHEN Color = 'Red' THEN ListPrice

             END) AS varchar), '') AS Red,

         COALESCE(CAST(AVG(CASE

               WHEN Color = 'Blue' THEN ListPrice

             END) AS varchar), '') AS Blue,

         COALESCE(CAST(AVG(CASE

               WHEN Color = 'Yellow' THEN ListPrice

             END) AS varchar), '') AS Yellow,

         COALESCE(CAST(AVG(CASE

               WHEN Color = 'Black' THEN ListPrice

             END) AS varchar), '') AS Black

FROM     Production.Product p

         JOIN Production.ProductSubcategory sc

           ON p.ProductSubcategoryID = sc.ProductSubcategoryID

GROUP BY sc.Name

ORDER BY Subcategory

GO

/* Partial results

 

Subcategory       Multi       Silver_Black      Silver            Grey

Mountain Bikes                                  1653.93    

Mountain Frames                                 685.65     

Panniers                                                          125.00

Pedals                        64.02      

*/

 

 

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