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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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