Convert comma separated string to table column

I have seen a few questions asking how to transform a delimited values into columns, so I thought I would talk about it here. In most cases it is recommended to use a string parser function to split the string; however, today I want to talk about another method. This method takes advantage of the XML data type that was introduced in SQL Server 2005. What is nice about the XML data type is it preserves the document order. The document order is critical because it guarantees the string is kept in the same order when it is converted to XML. Let’s start by creating a sample table with a few rows of data.


DECLARE @t TABLE(
ProductId INT,
ProductName VARCHAR(25),
SupplierId INT,
Descr VARCHAR(50)
)

INSERT INTO @t VALUES (1,'Product1',1,'A1,10in,30in,2lbs');
INSERT INTO @t VALUES (2,'Product2',2,'T6,15in,30in,');
INSERT INTO @t VALUES (3,'Product3',1,'A2,1in,,0.5lbs');
Okay now we have our sample data, let’s talk about our data. The column “Descr” contains 4 attributes of the product. The attributes that we are storing are Type,Length,Height, and Weight. Anyone of these attributes can be null in the string but MUST be represented in the string. For example, “A1,10in,30in,” has a comma at the end because the weight is unknown and a string like “A1,10in,,1lbs” has a empty space for height. If we do not have this space, how could we ever determine which attributes are associated with a particular column? The answer is there would be no real way of knowing with this method or any other method.


Now that we have laid all the ground work, it is time to start building our query. We will start off by creating a CTE (common table expression) making sure to convert our delimited string into valid XML. Below is the first part of our CTE (this is not the complete code).


;WITH cte (ProductId, ProductName,SupplierId,Prod_Attributes)
AS
(
SELECT
[ProductId],
[ProductName],
[SupplierId],
CONVERT(XML,''
+ REPLACE([Descr],',', '')
+ '') AS Prod_Attributes
FROM @t
)
You may be asking yourself, what does this code do? This CTE code selects all the data from the table @t, but it also converts the delimited string into valid XML. How does it do that? Well let’s break down the code to figure it out.


This is the conversion code of interest:


CONVERT(XML,''
+ REPLACE([Descr],',', '')
+ '') AS Prod_Attributes
This code takes the input string and uses the replace function to insert the XML tags, so that it has the look and feel of valid xml. For example, the string “A1,10in,30in,5lbs” will be transformed into



A1
10in
30in
5lbs

Note: It should be noted that we are using a comma delimitation. If your delimitation is different, you will need to change the delimiter in the replace function. The delimiter is between the column and the closing XML tag. In the code following, REPLACE([Descr],',', ' . the delimiter is denoted in Red.


Now that the product description values are in a valid XML format, we can easily get the values by using a hardcoded singleton value, as shown below.


;WITH cte (ProductId, ProductName,SupplierId,Prod_Attributes)
AS
(
SELECT
[ProductId],
[ProductName],
[SupplierId],
CONVERT(XML,''
+ REPLACE([Descr],',', '')
+ '') AS Prod_Attributes
FROM @t
)
SELECT
[ProductID],
[SupplierId],
Prod_Attributes.value('/Product[1]/Attribute[1]','varchar(25)') AS [Type],
Prod_Attributes.value('/Product[1]/Attribute[2]','varchar(25)') AS [Length],
Prod_Attributes.value('/Product[1]/Attribute[3]','varchar(25)') AS [Height],
Prod_Attributes.value('/Product[1]/Attribute[4]','varchar(25)') AS [Weight]
FROM cte
The singleton of each attribute element is denoted by a hardcoded value 1-4. If you have more columns you will need to specify more singletons. Here are the results:






So there you have it. An easy to implement solution to “pivot” or transform delimited values into columns.


**************** Update ********************


I have reworked my code to work with XML special characters. XML special characters are quite problematic but Brad Schulz, http://bradsruminations.blogspot.com/, has come up with a great method to work around the encoding issues. Here is the modified code:


SELECT
[ProductID],
[SupplierId],
NewXML.value('/Product[1]/Attribute[1]','varchar(25)') AS [Type],
NewXML.value('/Product[1]/Attribute[2]','varchar(25)') AS [Length],
NewXML.value('/Product[1]/Attribute[3]','varchar(25)') AS [Height],
NewXML.value('/Product[1]/Attribute[4]','varchar(25)') AS [Weight]
FROM @t t1
CROSS APPLY (SELECT XMLEncoded=(SELECT Descr AS [*] FROM @t t2 WHERE t1.ProductId = t2.[ProductId] FOR XML PATH(''))) EncodeXML
CROSS APPLY (SELECT NewXML=CAST(''+REPLACE(XMLEncoded,',','')+'' AS XML)) CastXML

Useful functions for SQL Server

/* In built function */
---Date Function
--1.

select getDate() -- to display current date
select GETUTCDATE() -- current date in UTC time

select * from dual

--2. DATEPART ( datepart , date )
SELECT DATEPART(Year, GETDATE()) AS 'Year' --year,yy,y
SELECT DATEPART(Month, GETDATE()) AS 'Month Number' --month mm, m
SELECT DATEPART(dayofyear,GETDATE())AS 'Day of Year' --dy, y
SELECT DATEPART(day,GETDATE()) AS 'Day' --dd, d
SELECT DATEPART(week,GETDATE()) AS 'Week' --wk, ww
SELECT DATEPART(weekday ,cast('02/18/2007'as datetime)) AS 'WeekDay' --GETDATE()) -- dw
SELECT DATEPART(hour,GETDATE()) AS 'Hour' -- hh
SELECT DATEPART( minute,GETDATE()) AS 'Minute' -- mi, n
SELECT DATEPART(second,GETDATE()) AS 'Second' -- ss, s
SELECT DATEPART(millisecond,GETDATE()) AS 'Millisecond' -- ms

--3. DATENAME ( datepart , date )
SELECT DATENAME(Year, GETDATE()) AS 'Year' --year,yy,y
SELECT DATENAME(Month, GETDATE()) AS 'Month Number' --month mm, m
SELECT DATENAME(dayofyear,GETDATE())AS 'Day of Year' --dy, y
SELECT DATENAME(day,GETDATE()) AS 'Day' --dd, d
SELECT DATENAME(week,GETDATE()) AS 'Week' --wk, ww
SELECT DATENAME(weekday ,cast('02/18/2007'as datetime)) AS 'WeekDay' --GETDATE()) -- dw
SELECT DATENAME(hour,GETDATE()) AS 'Hour' -- hh
SELECT DATENAME( minute,GETDATE()) AS 'Minute' -- mi, n
SELECT DATENAME(second,GETDATE()) AS 'Second' -- ss, s
SELECT DATENAME(millisecond,GETDATE()) AS 'Millisecond' -- ms

--4.Day Function
select Day(GETDATE()) AS 'Day'
select Month(GETDATE()) AS 'Month'
select Year(GETDATE()) AS 'Year'
select DATEPART("year",GETUTCDATE())

--5. DATEDIFF(datepart, startdate, endate)
SELECT DATEDIFF(Month,cast('01/18/2007'as datetime) ,GETDATE()) AS 'ExpMonth'

--6.DATEADD(datepart, number, date)
SELECT DATEADD(Month,3 ,GETDATE()) AS 'Last Day of training'

/* String Function
The following scalar functions perform an operation on a string input value and return a string or numeric value:*/
select lower('CYBAGE')
select upper('cybage')
select len('Cybage')
select DATALENGTH('Cybage')
select ascii('A')
select char(65)
select charindex('jil','jack and jil')
select charindex('jil','jack and jil',7)
select DIFFERENCE ( 'smith' ,'smeeth' )
select LEFT('jack and jil',7)
select Right('jack and jil',3)
SELECT QUOTENAME('abc"def')
select PATINDEX ( '%pattern%' ,'Testing pattern index function' )
select str(126.89,5,2)
select space(5)+'test'
select REPLACE ('Jack','J','B')
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
select ltrim(' cybage')
select rtrim(' cybage')
select reverse('test')
select substring('jack and jil',2,3)

DECLARE @nstring nchar(12)
SET @nstring = N'Åkergatan 24'
SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring))

select @@CONNECTIONS

select CURRENT_USER
SELECT DB_NAME()

--Conversion function
/*
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])*/
select * from emp where hiredate = '12/17/1980'
select * from emp where hiredate = cast('12/17/1980' as datetime)

/*Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )*/
select convert(datetime,'12/12/2007',103)
select CONVERT(XML, '',1)

select 'Salry of '+ename +' is '+ cast(sal as varchar) from emp

/*aggrigate function*/
select * from emp order by sal desc
select * from dept


select deptno,min(Sal)Min_Sal ,max(sal) Max_Sal,avg(sal) Avg_sal,count(*) No_of_Employee from emp group by deptno
select deptno,min(Sal)Min_Sal ,max(sal) Max_Sal,avg(sal) Avg_sal,count(*) No_of_Employee from emp group by deptno Having deptno=20

/*Department wise ,designation wise total employee */
SELECT deptno, job,count(empno) 'No_of_emp'
FROM emp GROUP BY deptno,job WITH rollup

/*Department wise ,designation wise total employee along with designation wise total employee*/
SELECT deptno, job,count(empno) 'No_of_emp'
FROM emp GROUP BY deptno,job WITH cube

Recursion For Binding Treeview

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
string substringDirectory;
public Form1()
{
InitializeComponent();
directoryTreeView.Nodes.Clear();

String path = "E:\\Mohit";

directoryTreeView.Nodes.Add(path);
PopulateTreeView(path, directoryTreeView.Nodes[0]);
}
public void PopulateTreeView(string directoryValue, TreeNode parentNode)
{
string[] directoryArray =
Directory.GetDirectories(directoryValue);

try
{
if (directoryArray.Length != 0)
{
foreach (string directory in directoryArray)
{
substringDirectory = directory.Substring(
directory.LastIndexOf('\\') + 1,
directory.Length - directory.LastIndexOf('\\') - 1);

TreeNode myNode = new TreeNode(substringDirectory);

parentNode.Nodes.Add(myNode);

PopulateTreeView(directory, myNode);
}
}
}
catch (UnauthorizedAccessException)
{
parentNode.Nodes.Add("Access denied");
} // end catch
}
}
}

CLR Stored Procedure Calling External Web Service - SQL Server 2005 Tutorials

SQL Server 2005 has a number of cool new developer features, such as Http Endpoint, XML DataType, etc. The CLR ( Common Language Runtime ) is also hosted inside of SQL Server 2005, allowing one to leverage the power of the .NET Framework and T-SQL in a power combination, exploiting each to do what it does best. A good example I previously wrote was to leverage regular expressions ( RegEx ) in .NET and CLR User Defined Functions ( UDF 's ) to put a check constraint on a table that verifies an email address is indeed an email address when entered into a table:

SQLCLR - Create CLR User-Defined Function ( UDF ) - Check Constraint on EmailAddress Column Using RegEx
In this example, I am exploring the ability to write a CLR stored procedure that calls an external web service from within the stored procedure before inserting a record in a table. The developer in me says that this is not something I would do in a production environment, but the hobbyist in me wanted to test this functionality just to see how it could be done.

The inspiration for this example came from another article, called Consuming a Web Service from a SQL Server 2005 CLR Assembly, in Simple Talk doing essentially the same thing. I ran across some problems while working through the tutorial which I thought I would highlight here as well as changed the scenario so it was easier to understand, more thorough in many cases, and showed off a few other things. However, the inspiring tutorial is very enightening, so I recommend reading it here.



CLR Stored Procedure Application Architecture
The architecture for this CLR stored procedure is fairly straight forward. The client of this application calls a CLR stored procedure, called InsertLocation, which accepts 3 input parameters: City, State, and Country.

InsertLocation calls the publicly available external web service, called Terraserver-USA web service, to get an aerial view map of the city as stored in its database of aerial images provided by the US Geological Survey (USGS).

The City, State, Country, and AerialMap is then stored in a table, called Locations.

The architecture can be briefly shown as follows:







Enabling CLR in SQL Server 2005
First things first, one needs to enable CLR in SQL Server 2005 by doing the following:



sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO


Create ClrWebServices Database and Make Database Trustworthy
Create the ClrWebServices Database. Since our database will be accessing external resources, you will need to make it trustworthy:



alter database ClrWebServices set trustworthy on


Create Locations Table
Create the Locations Table in the ClrWebServices Database:



USE [ClrWebServices]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Locations](
[ID] [int] IDENTITY(1,1) NOT NULL,
[City] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[State] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Country] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AerialMap] [image] NOT NULL,
CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


WSDL - Run WSDL.exe Utility to Generate Web Service Proxy
We need a c# proxy class that will be used to access the Terraserver-USA web service. The WSDL.exe application located in the Visual Studio SDK/Bin directly will do this for us:



wsdl /o:TerraService.cs /n:ClrWebServices.Text
http://www.terraserver-usa.com/TerraService2.asmx


This generates a file, called TerraService.cs, that has a public c# class within it, called TerraService, within the namespace ClrWebServices.Test.



Create CLR Stored Procedure
Create the CLR Stored Procedure that uses TerraService to obtain a aerial view map of the city and then store the city, state, country, and map in the Locations Table:



using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using ClrWebServices.Test;


public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertLocation(SqlString city,
SqlString state, SqlString country)
{
using (SqlConnection cn =
new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.Locations
(City,State,Country,AerialMap) VALUES
(@City, @State, @Country, @Image)";

using (SqlCommand insertCommand =
new SqlCommand(query,cn))
{
SqlParameter[] sqlParams =
new SqlParameter[4];
sqlParams[0] =
new SqlParameter("@City", city);
sqlParams[1] =
new SqlParameter("@State", state);
sqlParams[2] =
new SqlParameter("@Country", country);
sqlParams[3] =
new SqlParameter("@Image",
GetLocationImage(city,state,country));

insertCommand.Parameters.AddRange(sqlParams);

cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}
}

public static byte[] GetLocationImage(SqlString city,
SqlString state, SqlString country)
{
TerraService service = new TerraService();

Place place = new Place();
place.City = city.ToString();
place.State = state.ToString();
place.Country = country.ToString();

PlaceFacts facts = service.GetPlaceFacts(place);

TileMeta meta = service.GetTileMetaFromLonLatPt
(facts.Center, 1,
ClrWebServices.Test.Scale.Scale16m);
return service.GetTile(meta.Id);
}
};


This file is saved as InsertLocation.cs.



Compile CLR Stored Procedure with Web Services Proxy
Now one needs to compile the CLR Stored Procedure and Web Services Proxy into a single DLL as well as create XML Serialization Code, because dynamic XML Serialization is not allowed in SQL Server.



// Creates InsertLocation.dll
csc /t:library InsertLocation.cs TerraService.cs

// Generate XML Serialization Code
// Creates InsertLocation.XmlSerializers.dll
sgen /a:InsertLocation.dll


Create Assemblies and Stored Procedure
The very last piece is to create the assemblies and stored procedures in SQL Server 2005.



CREATE ASSEMBLY ClrWebServices
FROM 'C:\ClrWebServices\InsertLocation.dll'
WITH PERMISSION_SET = UNSAFE;
GO


CREATE ASSEMBLY [ClrWebServices.XmlSerializers]
FROM 'C:\ClrWebServices\InsertLocation. XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO


CREATE PROCEDURE InsertLocation(@city nvarchar(200),
@state nvarchar(200), @country nvarchar(200))
AS
EXTERNAL NAME ClrWebServices.StoredProcedures .InsertLocation
GO


Here you can see all the major players in out CLR stored procedure example:









Test CLR Stored Procedure
Now one can go ahead and execute the stored procedure, InsertLocation, from withing SQL Server Management Studio. While executing the CLR stored procedure will execute a couple of web services calls to Terraserver to get the proper aerial view map. Don't expect it to be fast :) Check the Locations Table to verify the image has been properly stored in the AerialMap column.







Conclusion
CLR Stored Procedures can consume XML web services inside them. Although it is technically possible, this may not be the best architectural approach in your .NET applications. Proceed with caution, but have fun.