T-SQL: Pivot but for semicolon-separated values instead of columns.

Question

I've got semicolon-separated values in a column Values in my table:
Values
1;2;3;4;5

I would like to transform it in a procedure to have there values as rows:
Values
1
2
3
4
5

How could I do it in T-SQL?

Answer 1

Take a look here, for example (method 6).

Answer 2

It's not the most elegant approach, but this might be worth a try. It creates a Sql Command as a string, and at the end executes it.

DECLARE @Values VARCHAR(8000)
-- Flatten all values lists into one string
SET @Values = REPLACE(REPLACE((SELECT [Value] FROM [dbo.MyTable] FOR XML PATH('')), '<Value>', ''), '</Value>', ';')
SET @Values = SUBSTRING(@Values, 0, LEN(@Values))

DECLARE @SeparatorIndex INT
SET @SeparatorIndex = (SELECT TOP 1 PATINDEX('%[;]%', @Values))

DECLARE @InsertClause VARCHAR(50)
SET @InsertClause = 'INSERT INTO [dbo.MyTable] VALUES ('

DECLARE @SQL VARCHAR(500)
SET @SQL = @InsertClause + SUBSTRING(@Values, 0, @SeparatorIndex) + '); '

SET @Values = RIGHT(@Values, LEN(@Values) - (@SeparatorIndex - 1))

SET @SQL = REPLACE(@SQL + (SELECT (REPLACE(@Values, ';', '); ' + @InsertClause))) + ')', '; )', '')

EXEC (@SQL)

The command ends up (in Sql Server 2005) as:

INSERT INTO [dbo.MyTable] VALUES (1); INSERT INTO [dbo.MyTable] VALUES (2); INSERT INTO [dbo.MyTable] VALUES (3); INSERT INTO [dbo.MyTable] VALUES (4); INSERT INTO [dbo.MyTable] VALUES (5) ...'

Answer 3

Do you actually mean, "rows," as in, "tuples," (so you can insert the data into another table, one element per row) or do you mean you want the data displayed vertically?

I'd think a string Replace (look up T-SQL's String Functions) would do the trick, no? Depending on the output target, you'd replace ; with CRLF or
. You could even use Replace to create dynamic SQL Insert statements that could be executed by the SP to do row inserts (if that was your intent).

For presentation purposes, this is bad practice.

If it is purely for presentation and you are permitted, I'd output everything as XML then XSLT it any way you want. Honestly, I don't remember the last time I operated directly on a recordset. I always output to XML.

Answer 4

Solution 1(using xml):

declare @str varchar(20)
declare @xml as xml
set @str= '1;2;3;4;5'
SET @xml = cast(('<x>'+replace(@str,';' ,'</x><x>')+'</x>') as xml)
SELECT col.value('.', 'varchar(10)') as value FROM @xml.nodes('x') as tbl(col)

Solution 2(using recursive cte)

declare @str as varchar(100)
declare @delimiter as char(1)
set @delimiter = ';'
set @str = '1;2;3;4;5' -- original data
set @str = @delimiter + @str + @delimiter

;with num_cte as
(     
      select 1 as rn
      union all
      select rn +1 as rn 
      from num_cte 
      where rn <= len(@str)
)
, get_delimiter_pos_cte as
( 
      select      
                  ROW_NUMBER() OVER (ORDER BY rn) as rowid, 
                  rn as delimiterpos            
      from num_cte
      cross apply( select substring(@str,rn,1)  AS chars) splittedchars 
      where chars = @delimiter
)

select substring(@str,a.delimiterpos+1 ,c2.delimiterpos - a.delimiterpos - 1) as Countries
from get_delimiter_pos_cte a
inner join get_delimiter_pos_cte c2 on c2.rowid = a.rowid+1
option(maxrecursion 0)