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:
Post a Comment

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.

Comments

Post a Comment

Popular posts from this blog

CREATE DATABASE (SMO)

Create a SQL Server Database by using SMO In this small tutorial I will show you how to create a database with SMO and C# in Visual Studio 2008. I will use a Console application. Prerequisites: SQL Server 2005 is installed Visual Studio 2008 is installed Code Assemblies Your C# project must reference: System System.Data Micrososft.SqlServer.ConnectionInfo Micrososft.SqlServer.Management.Sdk.Sfc Micrososft.SqlServer.Smo Namespaces The source class file must use: System System.Data.SqlClient Micrososft.SqlServer.Management.Common Micrososft.SqlServer.Management.Smo class ConsoleSmo {    public static Main()   {      string connLocal = "Data Source=(local); Integrated Security=SSPI;"      SqlConnection connection = new SqlConnection (connLocal);      Server server = new Server ( new ServerConnection (connection));      Database db = new Database (server, “TestDb” );     db.Create();     connection.Close();   } } Explanations This code opens the connectio
Read more