split the Data comma separated values into rows:
step :1 Create the Document_Master Table
CREATE TABLE [dbo].[Document_Master ](
[ID] [bigint] IDENTITY(1,1) ,
[CountryCode] [nvarchar](2),
[cityCode] [nvarchar](max) ,
[DocumentName] [nvarchar](50) ,
[DocumentURLPath] [nvarchar](250) ,
[DocumentPath] [nvarchar](250) ,
[status] [bit] )
GO
step :2 Dump the temp Data into the table.
step :3 create the temp table.
drop table #yourTable
DECLARE @yourTable TABLE(ID bigint,ContryCode nvarchar(2),cityCode NVARCHAR(max))
SELECT * INTO #yourTable
FROM ( SELECT ID,countryCode,cityCode FROM Document_Master ) AS x
GO
WITH CTE
AS
(
SELECT countryCode,
[xml_val] = CAST('<t>' + REPLACE(cityCode,',','</t><t>') + '</t>' AS XML)
FROM #yourTable
)
SELECT distinct countryCode,
cityCode = col.value('.','VARCHAR(max)')
FROM CTE
CROSS APPLY [xml_val].nodes('/t') CA(col)
order by countryCode,cityCode asc
step : show the result.
Split the rows into comma separated values in sql server?
create table #user (username varchar(25))
insert into #user (username) values ('HARI')
insert into #user (username) values ('ravi')
insert into #user (username) values ('RAJU')
declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + username + ', ' from #user
select SUBSTRING(@tmp, 0, LEN(@tmp))
Output:
