Snippet

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

Seaching Cognos 10 Content Store for an XML string

Seaching Cognos 10 Content Store for an XML string

We recently migrated our knowledgebase from Collab to sharepoint, which meant all reports that had links to collab needed to be identified and edited. This query proved helpful – it searches the content store for occurrences of the old string, ‘collaboration.au’, and displays the report name and location. The query does take a while to run though – almost 40 minutes against our beast-mode content store…

read more