Friday, April 17, 2009

How to list all comma separated values in all columns of a tabele as single.

How to list all comma separated values in all columns of a table as single column.
If you have a table in SQl like:

[Figure-1 Table with RowData]





and if you want this tables select data as table like:

[Figure-2 RESULT]





we can use XML to SQL Using XML datatype of SQl server 2005 and above.XML datatype
is new datatype in sql server 2005. We can user SQL Statements to select data from XML string.this xml datatype helps us great to do many complex operation in less line of code.
here is the example.

--Create tabel and insert data to see this example in action.
CREATE TABLE [dbo].[User_mst](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50)

CONSTRAINT [PK_User_mst] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

--Insert Data
insert into [User_mst1] ([Name]) values('Haresh,Herry')
insert into [User_mst1] ([Name]) values('Paresh,Pariyo,Perry')
insert into [User_mst1] ([Name]) values('Ajay,Sinh,Bapu')
insert into [User_mst1] ([Name]) values('Abhijit')
after completing object creation we can use followint Query to select all comma separated values of each row to a single column..

--Declared for storing Selected XML
declare @ColumnAsXmlString varchar(8000)
--select Comumn as XML
set @ColumnAsXmlString=(select replace([Name],',','') as NM from User_mst for XML path(''))
--replace <with <>
set @ColumnAsXmlString=replace(@ColumnAsXmlString,'<','<') set @ColumnAsXmlString=replace(@ColumnAsXmlString,'>','>')

-- @ColumnXML as XML datatype
declare @ColumnXML as xml
--Cast string to xml
set @ColumnXML= cast(@ColumnAsXmlString as xml)
--fire Query on XMLdatatype(@ColumnXML) to select All values in XML string as Table

select ParamValues.[Name].value('.','VARCHAR(200)') as [Name]
FROM @ColumnXML.nodes('/NM') as ParamValues([Name]) .
After Runnig this you will get result (Fugure-2).
Thanks.