datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
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