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

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 proc开发者_如何学编程edure to have there values as rows:

Values

1

2

3

4

5

How could I do it in T-SQL?

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)

The thing that struck me as possibly leaving room for an additional answer, or additional improvement was that most of the answers/links given were how to split values like this for a single scalar value as opposed to how to apply that kind of splitting logic for a column of values in a table.

I include both a numbers table solution and an XML solution. The XML solution was inspired by the earlier post priyanka.sarkar. I think that a numbers table solution, using an actual numbers table instead of the CTE as in the below solution is probably the fastest, but the XML approach deserves to be developed upon because it's really nice looking.

So, here goes my attempt.

CREATE PROCEDURE PARSE_DELIMITED_VALUES
AS
WITH FIRST_NUMBERS (N) AS (
        SELECT 1 UNION ALL SELECT 1
), SECOND_NUMBERS (N) AS (
        SELECT E1.N
        FROM FIRST_NUMBERS E1
        CROSS JOIN FIRST_NUMBERS E2
), THIRD_NUMBERS (N) AS (
        SELECT E1.N
        FROM SECOND_NUMBERS E1
        CROSS JOIN SECOND_NUMBERS E2
), FOURTH_NUMBERS (N) AS (
        SELECT E1.N
        FROM THIRD_NUMBERS E1
        CROSS JOIN THIRD_NUMBERS E2
), FIFTH_NUMBERS (N) AS (
        SELECT E1.N
        FROM FOURTH_NUMBERS E1
        CROSS JOIN FOURTH_NUMBERS E2
), NUMBERS (N) AS (
        SELECT N
        FROM NUMBERS
        WHERE N <= 8000 /*adjust these as needed to come up with a max number equal to the max character length allowed in the Values column*/
        /*or better yet, if you can, just remove this first...numbers... header stuff so long as you create a temp or permanent table that contains the same numbers to work with*/
)
SELECT SUBSTRING(
    MYTABLE.Values,
    CASE
    WHEN NUMBERS.NUMBER = 1 THEN 1
    ELSE NUMBERS.NUMBER + 1
    END,
    CASE CHARINDEX(';', MYTABLE.Values, NUMBERS.NUMBER + 1)
    WHEN 0 THEN LEN('^' + MYTABLE.Values + '^') - 2 + 1
    ELSE CHARINDEX(';', MYTABLE.Values, NUMBERS.NUMBER + 1)
    END
    - CASE
    WHEN NUMBERS.NUMBER = 1 THEN 1
    ELSE NUMBERS.NUMBER + 1
    END
) AS PARSED_VALUE
FROM MYTABLE
INNER JOIN NUMBERS
ON NUMBERS.NUMBER <= LEN('^' + MYTABLE.Values + '^') - 2
AND (
    NUMBERS.NUMBER = 1
    OR SUBSTRING(MYTABLE.Values, NUMBERS.NUMBER, 1) = ';'
)
GO

-- if your values column can contain NULL values I would change the join at the end as follows:  
--from INNER JOIN NUMBERS  
--to LEFT OUTER JOIN NUMBERS  

The above would probably be most performant if the WITH NUMBERS ... CTEs were replaced by a temporary or permanent table containing the same numeric values. On the other hand the CTE does the job and keeps it more in one place.

CREATE PROCEDURE PARSE_DELIMITED_VALUES
AS
SELECT E.x.value('.', 'VARCHAR(MAX)') AS PARSED_VALUE
FROM (
    SELECT CAST('<x>' + REPLACE(Values, ';', '</x><x>') + '</x>' AS XML) my_x
    FROM MYTABLE
) TT
CROSS APPLY my_x.nodes('/x') AS E(x)
GO

-- if your values column can contain NULL values I would change the join at the end as follows:  
from `CROSS APPLY`  
to `OUTER APPLY`  

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) ...'

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.