Posts

Showing posts from May, 2014

Hey, I rewrote UNION by way of UNPIVOT

Yesterday a team mate of mine asked me to review a piece of SQL script that looked like a good candidate for UNPIVOT. Below is the amended version of that code (I obfuscated the data and added a simple data initialization using the WITH clause): ;with u as ( -- data setup select * from ( values(1, 1, 1, 1), (2, 1, 1, 0), (3, 1, 0, 0), (4, 0, 0, 0) ) t(UserID, HasDbSkills, HasBackendSkills, HasFrontEndSkills) ) select * from ( select UserID, 0 as SkillType, 1 as DevelopmentSkill from u where HasDbSkills = 1 union select UserID, 1 as SkillType, 1 as DevelopmentSkill from u where HasBackendSkills = 1 union select UserID, 2 as SkillType, 1 as DevelopmentSkill from u where HasFrontEndSkills = 1 ) t(UserID, SkillType, DevelopmentSkill) order by UserID, SkillType Indeed, after a quick analysis, this script turned out to be ripe for UNPIVOT:

Initializing xslt variable with static content

I had to use a variable in an xslt template. The value for that variable would be returned by a stored procedure at a SQL Server DB. So, I decided to populate the variable with static content:     <xsl:variable name="hideColumns" select ="'ISBN,author_id,vendor_id'" /> Populated with the static content, the variable proved to be a good replacement for the real-life data. So, as soon as the stored procedure was deployed, my xslt template was readily updated with the appropriate select statement in the hideColumns variable declaration.