SQL Server code to pivot Key Value Pairs into fixed Colums

Today’s requirement was to translate 550k rows of product data from a long list of name / value pairs into a wide table with up to 15 name value pairs per product.

transform-and-combine

 

 

 

 

 

I tried a few techniques to achieve this, and found SQL Server’s windowing functions to do a pretty good job of building a cross-tab from this flat list.

Note code is for SQL 2016, may work on 2012 but unlikely for prior versions

select t1.[Product Code] product_code,
max(case when rownum = 1 then t1.Attribute else NULL end) as Attribute_1_Name__c,
max(case when rownum = 1 then t1.[Attribute Value] else NULL end) as Attribute_1_Value__c,
max(case when rownum = 2 then t1.Attribute else NULL end) as Attribute_2_Name__c,
max(case when rownum = 2 then t1.[Attribute Value] else NULL end) as Attribute_2_Value__c,
max(case when rownum = 3 then t1.Attribute else NULL end) as Attribute_3_Name__c,
max(case when rownum = 3 then t1.[Attribute Value] else NULL end) as Attribute_3_Value__c,
max(case when rownum = 4 then t1.Attribute else NULL end) as Attribute_4_Name__c,
max(case when rownum = 4 then t1.[Attribute Value] else NULL end) as Attribute_4_Value__c,
max(case when rownum = 5 then t1.Attribute else NULL end) as Attribute_5_Name__c,
max(case when rownum = 5 then t1.[Attribute Value] else NULL end) as Attribute_5_Value__c,
max(case when rownum = 6 then t1.Attribute else NULL end) as Attribute_6_Name__c,
max(case when rownum = 6 then t1.[Attribute Value] else NULL end) as Attribute_6_Value__c,
max(case when rownum = 7 then t1.Attribute else NULL end) as Attribute_7_Name__c,
max(case when rownum = 7 then t1.[Attribute Value] else NULL end) as Attribute_7_Value__c,
max(case when rownum = 8 then t1.Attribute else NULL end) as Attribute_8_Name__c,
max(case when rownum = 8 then t1.[Attribute Value] else NULL end) as Attribute_8_Value__c,
max(case when rownum = 9 then t1.Attribute else NULL end) as Attribute_9_Name__c,
max(case when rownum = 9 then t1.[Attribute Value] else NULL end) as Attribute_9_Value__c,
max(case when rownum = 10 then t1.Attribute else NULL end) as Attribute_10_Name__c,
max(case when rownum = 10 then t1.[Attribute Value] else NULL end) as Attribute_10_Value__c,
max(case when rownum = 11 then t1.Attribute else NULL end) as Attribute_11_Name__c,
max(case when rownum = 11 then t1.[Attribute Value] else NULL end) as Attribute_11_Value__c,
max(case when rownum = 12 then t1.Attribute else NULL end) as Attribute_12_Name__c,
max(case when rownum = 12 then t1.[Attribute Value] else NULL end) as Attribute_12_Value__c,
max(case when rownum = 13 then t1.Attribute else NULL end) as Attribute_13_Name__c,
max(case when rownum = 13 then t1.[Attribute Value] else NULL end) as Attribute_13_Value__c,
max(case when rownum = 14 then t1.Attribute else NULL end) as Attribute_14_Name__c,
max(case when rownum = 14 then t1.[Attribute Value] else NULL end) as Attribute_14_Value__c,
max(case when rownum = 15 then t1.Attribute else NULL end) as Attribute_15_Name__c,
max(case when rownum = 15 then t1.[Attribute Value] else NULL end) as Attribute_15_Value__c

from
(
select
[Product Code],
ROW_NUMBER() over (PARTITION by [Product Code] order by Attribute) as rownum,
Attribute,
[Attribute Value]
from dwh_stage.products_secondary_inc
) t1

group by [Product Code]

0 Comments

You can be the first one to leave a comment.

Leave a Comment