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 apply PIVOT for many-to-many relationship?

Execute the following SQL Server T-SQL scripts in Microsoft SSMS Query Editor to demonstrate the application of the PIVOT operator for the generation of a crosstab query with Boolean result values.

-- SQL crosstab with pivot for Boolean values

-- SQL select into create table

USE tempdb;

SELECT DISTINCT Category = psc.Name,

                Size

INTO   SubCategorySizeUsage

FROM   AdventureWorks2008.Production.Product p

    INNER JOIN AdventureWorks2008.Production.ProductSubcategory psc

        ON p.ProductSubcategoryID = psc.ProductSubcategoryID

WHERE  Size IS NOT NULL

       AND p.ProductSubcategoryID IS NOT NULL

GO

-- T-SQL select to check sample data in rows (vertical)  

SELECT   *

FROM     SubCategorySizeUsage

ORDER BY Category

GO

 

/*  

Category    Size

Bib-Shorts  L

Bib-Shorts  M

Bib-Shorts  S

Gloves      L

Gloves      M

Gloves      S

.....

*/

 

-- T-SQL hard-wired column list for the PIVOT operator

DECLARE  @Sizes VARCHAR(MAX)

SET @Sizes = ''

SELECT @Sizes = @Sizes + ', ' + QUOTENAME(Size)

FROM   (SELECT DISTINCT Size

        FROM   SubCategorySizeUsage) x

SELECT @Sizes = STUFF(@Sizes,1,1,'')

SELECT @Sizes

GO

/* PIVOT list

 [38], [40], [42], [44], [46], [48], [50], [52], [54], [56], [58], [60], [62], [70], [L], [M], [S], [XL]

  */

SELECT pvt.*

FROM   (SELECT Category,

               Size,

               SizeExist = 1

        FROM   SubCategorySizeUsage

        UNION

        SELECT x.Category,

               y.Size,

               0

        FROM   SubCategorySizeUsage x

               CROSS JOIN SubCategorySizeUsage y) cc

       PIVOT

       (sum(SizeExist)

        FOR Size IN (  [38], [40], [42], [44], [46], [48], [50], [52], [54], [56], [58], [60], [62], [70], [L], [M], [S], [XL]) ) AS pvt

GO

 

/* Results: Many-to-many relationship in crosstab(matrix) report format

            0 means no relationship, 1 means relationship

 

 

Category 38 40 42 44 46 48 50 52 54 56 58 60 62 70 L M S XL
Bib-Shorts 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0
Gloves 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0
Hydration Packs 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0
Jerseys 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1
Mountain Bikes 1 1 1 1 1 1 0 1 0 0 0 0 0 0 0 0 0 0
Mountain Frames 1 1 1 1 1 1 0 1 0 0 0 0 0 0 0 0 0 0
Road Bikes 1 1 1 1 0 1 0 1 0 1 1 1 1 0 0 0 0 0
Road Frames 1 1 1 1 0 1 0 1 0 1 1 1 1 0 0 0 0 0
Shorts 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1
Socks 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0
Tights 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0
Touring Bikes 0 0 0 1 1 0 1 0 1 0 1 1 1 0 0 0 0 0
Touring Frames 0 0 0 1 1 0 1 0 1 0 1 1 1 0 0 0 0 0
Vests 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0

*/

 

-- Cleanup

DROP TABLE SubCategorySizeUsage

GO

 

Related link: http://www.sqlusa.com/bestpractices2005/crosstabwithpivot/

 

Exam Prep 70-461
Exam 70-461