I want to split the two columns into two rows in a single table separated by ‘;’ in Sql Server 2008. Please help me to resolve to solve this. Columns like:
1;2;3;4;5;6;7; and a;b;c;d;e;f;g;
Output Rows like:
1 a
2 b
3 c
4 d
5 e
6 f
7 g
First you are going to need a split function as such :
CREATE function [dbo].[Split]
(
@string nvarchar(max),
@delimiter nvarchar(20)
)
returns @table table
(
[Value] nvarchar(max)
)
begin
declare @nextString nvarchar(max)
declare @pos int, @nextPos int
set @nextString = ''
set @string = @string + @delimiter
set @pos = charindex(@delimiter, @string)
set @nextPos = 1
while (@pos <> 0)
begin
set @nextString = substring(@string, 1, @pos - 1)
insert into @table
(
[Value]
)
values
(
@nextString
)
set @string = substring(@string, @pos + len(@delimiter), len(@string))
set @nextPos = @pos
set @pos = charindex(@delimiter, @string)
end
return
end
Then using this code :
SELECT col1.Value as val1,
col2.Value as val2
FROM
(SELECT Value,
ROW_NUMBER() over(order by value asc) as rownum
FROM (
VALUES('1;2;3;4;5;6;7')
) valued(X) CROSS APPLY
DBO.SPLIT(X,';') AS SPLITEDCOL ) as col1 INNER JOIN
(SELECT Value,
ROW_NUMBER() over(order by value asc) as rownum
FROM (
VALUES('A;B;C;D;E;F;G')
) valued1(X) CROSS APPLY
DBO.SPLIT(X,';') AS SPLITEDCOL ) as col2
ON COL1.rownum = col2.rownum
if your two columns are from a table you can select them as such :
SELECT Value,
ROW_NUMBER() over(order by value asc) as rownum
FROM YourTable CROSS APPLY
DBO.SPLIT(YourColumnName,';') AS SPLITEDCOL
Note that 1 subset using cross apply is necessary for each column you want to return in rows
Hope this helps
Declare @ID as Varchar(1000)
set @ID = '1;2;3;4;5;6;7;'
SELECT
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS ID
FROM
(
SELECT CAST('<XMLRoot><RowData>' + REPLACE(@ID,';','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
DECLARE @Table1 TABLE(ID INT, Value char)
INSERT INTO @Table1 VALUES (1,'a'),(1,'b'),(1,'c'),(1,'d')
SELECT STUFF((SELECT '; ' + CAST(ID AS VARCHAR(10)) [text()]
FROM @Table1
WHERE ID = t.ID
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') ID
,STUFF((SELECT '; ' + CAST(Value AS VARCHAR(10)) [text()]
FROM @Table1
WHERE ID = t.ID
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') value
FROM @Table1 t
