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

CREATE DATABASE (ADO.NET and C#)

Create a SQL Server Database Programmatically

You need to create a SQL Server database programmatically by using ADO.NET and C#. Here is a quick recipe how to do that.
using System;
using System.Data.SqlClient;

SqlConnection localConn = new SqlConnection ("(local); Integrated security=SSPI; database=master");
string SQLCommand = "CREATE DATABASE TestDb";

SqlCommand DbCreate = new SqlCommand(SQLCommand, localConn);
try
{
  localConn.Open();
  DbCreate.ExecuteNonQuery();
  Console.WriteLine("DataBase is Created Successfully");
}
catch (System.Exception ex)
{
  Console.WriteLine(ex.ToString());
}
finally
{
  if (localConn.State == ConnectionState.Open)
  {
    myConn.Close();
  }
}
This code creates a database similar to the SQL Server Model database and places its files to the default location. If you want to create a database with custom properties, please check out MSDN.

Comments

Post a Comment

Popular posts from this blog

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

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