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

*/

-- Cleanup

DROP TABLE SubCategorySizeUsage

GO

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