SQL Server

SQL Server code to pivot Key Value Pairs into fixed Colums

Square peg and a round hole.  Metaphor for a misfit or nonconformist.

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.           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.

read more

Seeding an integer sort code into a Data Warehouse from a varchar

Seeding an integer sort code into a Data Warehouse from a varchar

I found this a bit of a challenge this morning, and as it’s my first use of windowing functions in SQL Server I thought I’d share. Our requirement was to add a sort code to our dimension tables for various levels. For some reason many of our business keys are varchars, however I really wanted to get sort codes as integers. Here’s the code: This generates three distinct sort keys, one for each level. Quite nifty I reckon.                       Long term we’ll look to move this stuff into a source system somewhere, but for now, she’s sorted.

read more