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.

No comments:

Post a Comment

Comments posted on ASP.Net Ajax Tutorials Blog are moderated and will be approved only if they are on-topic and not abusive. Please email me or my team for tech-support or blogging related questions. Avoid including website URLs in your comments - Thanks Author