single column to rows in sqlserver

Question

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:

Possible duplicate of [Split comma separated string table row into separate rows using TSQL](https://stackoverflow.com/questions/30920483/split-comma-separated-string-table-row-into-separate-rows-using-tsql) — , Sep 08 '17 at 08:00

score 1 · Accepted Answer · answered Sep 08 '17 at 07:56

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

score 0 · Answer 2 · answered Sep 08 '17 at 07:30

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)

score 0 · Answer 3 · answered Sep 08 '17 at 08:04

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

single column to rows in sqlserver

3 Answers3