SQL Server 2008, Pivot based on multiple columns-CodePudding

I am using SQL Server 2008, and I have a simple table:

Product.SKU
Product.Model
Product.Color
Product.Size

With the following 9 rows:

SKU	Model	Color	Size
1	PC	Blue	Normal
2	PC	Red	Normal
3	MAC	Silver	Normal
4	PC	Green	Normal
5	Mac	Blue	Normal
6	Phone	Blue	Normal
7	PC	Blue	Large
8	PC	Red	Large
9	MAC	Silver	Large

I want to create a SQL view to pivot other possible color options for each model/Size combo, that would display like the following:

View.Model
View.Size
View.SKU
View.Color
View.ALTSKU1
View.ALTColor1
View.ALTSKU2
View.ALTColor2
View.ALTSKU3
View.ALTColor3
etc. etc.

Model	Size	SKU	Color	AltSKU1	AltColor1	AltSKU2	AltColor2
PC	Normal	1	Blue	2	Red	4	Green
PC	Large	7	Blue	8	Red	NULL	NULL
Mac	Normal	3	Silver	5	Blue	NULL	NULL
Mac	Large	9	NULL	NULL	Blue	NULL	NULL
Phone	Normal	6	Blue	NULL	NULL	NULL	NULL

CodePudding user response：

I don't think you can easily pivot on multiple columns using built-in functionality, but you can use the ROW_NUMBER() window function together with conditional aggregation to achieve the desired effect.

Something like:

WITH CTE_Data AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Model, Size ORDER BY SKU) AS RowNumber
    FROM #Data
)
SELECT
    CD.Model, CD.Size
    , SKU       = MAX(CASE WHEN CD.RowNumber = 1 THEN CD.SKU   END)
    , Color     = MAX(CASE WHEN CD.RowNumber = 1 THEN CD.Color END)
    , AltSKU1   = MAX(CASE WHEN CD.RowNumber = 2 THEN CD.SKU   END)
    , AltColor1 = MAX(CASE WHEN CD.RowNumber = 2 THEN CD.Color END)
    , AltSKU2   = MAX(CASE WHEN CD.RowNumber = 3 THEN CD.SKU   END)
    , AltColor2 = MAX(CASE WHEN CD.RowNumber = 3 THEN CD.Color END)
FROM CTE_Data CD
GROUP BY Model, Size
ORDER BY Model, Size

Yielding the following result:

Model	Size	SKU	Color	AltSKU1	AltColor1	AltSKU2	AltColor2
MAC	Large	9	Silver	null	null	null	null
MAC	Normal	3	Silver	5	Blue	null	null
PC	Large	7	Blue	8	Red	null	null
PC	Normal	1	Blue	2	Red	4	Green
Phone	Normal	6	Blue	null	null	null	null

See this db<.fiddle for a demo.

To allow for an unknown number of alternate SKUs, you can use dynamic SQL to generate a select that matches the maximum number of SKUs per Model/Size combination.

Here is the version using dynamic SQL:

DECLARE @AltSkuItemsTemplate VARCHAR(MAX) = '
        , AltSKU<N1>   = MAX(CASE WHEN CD.RowNumber = <N2> THEN CD.SKU   END)
        , AltColor<N1> = MAX(CASE WHEN CD.RowNumber = <N2> THEN CD.Color END)
'

DECLARE @AltSkuItems VARCHAR(MAX)= ISNULL((
    SELECT REPLACE(REPLACE(
        @AltSkuItemsTemplate
        , '<N1>', CONVERT(VARCHAR, RN.RowNumber - 1))
        , '<N2>', CONVERT(VARCHAR, RN.RowNumber))
    FROM (
        SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY Model, Size ORDER BY SKU) AS RowNumber
        FROM #Data
    ) RN
    WHERE RN.RowNumber >= 2
    ORDER BY RN.RowNumber
    FOR XML PATH(''),TYPE
).value('text()[1]', 'varchar(max)'), '')

--PRINT @AltSkuItems

DECLARE @SqlTempate VARCHAR(MAX) = '
    WITH CTE_Data AS (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY Model, Size ORDER BY SKU) AS RowNumber
        FROM #Data
    )
    SELECT
        CD.Model, CD.Size
        , SKU       = MAX(CASE WHEN CD.RowNumber = 1 THEN CD.SKU   END)
        , Color     = MAX(CASE WHEN CD.RowNumber = 1 THEN CD.Color END)
<AltSkuItems>
    FROM CTE_Data CD
    GROUP BY Model, Size
    ORDER BY Model, Size
'

DECLARE @Sql VARCHAR(MAX) = REPLACE(@SqlTempate, '<AltSkuItems>', @AltSkuItems)

--PRINT @Sql
EXEC (@Sql)

The FOR XML PATH(''),TYPE in the above is a technique for concatenating multiple results into a single string. The .value('text()[1]', 'varchar(max)') extracts the text from the resulting XML (and cleanly handles any special characters, such as carriage returns).

See this updated db<>fiddle for a demo of both of the above.