Turning string into rows

Question

I have an old vintage system with a table looking like this.

OptionsTable
id   options
===  ========================
101  Apple,Banana            
102  Audi,Mercedes,Volkswagen

In the application that consumes the data, a function will break down the options column into manageable lists and populate dropdowns etc.

The problem is that this kind of data isn't very SQL friendly, making it difficult to make ad-hoc queries and reports.

To that end, I'd like to transform the data into a friendlier view, looking like this:

OptionsView
id   name        value
===  ==========  =====
101  Apple           1
101  Banana          2
102  Audi            1
102  Mercedes        2
102  Volkswagen      3

Now, there have been some topics on splitting string into rows in t-sql (Turning a Comma Separated string into individual rows comes to mind), but apart from splitting the strings into rows, I also need to generate values based on the position in the string.

The plan is to make a view that hides the uglines of the original table.

It will be used in a join with the table housing the answers in order to make ad-hoc statistical queries.

Is there a good way of doing this without having to use cursors etc?

Answer 1

Perhaps adding a udf is overkill for your needs, but I created a split function a long time ago that returns the value, the startposition within the string and the index. With it, the usage in this scenario would be:

select id, String as [Name], ItemIndex as value from OptionsTable
outer apply dbo.Split(options, ',')

Results:

id  Name    value
101 Apple   1
101 Banana  2
102 Audi    1
102 Mercedes    2
102 Volkswagen  3

And the split function (unrevised since then):

ALTER function [dbo].[Split] ( 
@StringToSplit varchar(2048),
@Separator varchar(128))
returns table as return

with indices as
( 
select 0 S, 1 E, 0 I
union all
select E, charindex(@Separator, @StringToSplit, E) + len(@Separator) , I + 1
from indices
where E > S 
)
select substring(@StringToSplit,S, 
case when E > len(@Separator) then e-s-len(@Separator) else len(@StringToSplit) - s + 1 end) String
,S StartIndex, I ItemIndex        
from indices where S >0

Answer 2

This should work for you:

DECLARE @OptionsTable TABLE
(
    id INT
    , options VARCHAR(100)
);

INSERT INTO @OptionsTable (id, options)
VALUES (101, 'Apple,Banana')
    , (102, 'Audi,Mercedes,Volkswagen');

SELECT OT.id, T.name, t.value
FROM @OptionsTable AS OT
CROSS APPLY (
    SELECT T.column1, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM dbo.GetTableFromList(OT.options, ',') AS T
    ) AS T(name, value);

Here dbo.GetTableFromList is a split string function.
CROSS APPLY executes this function for each row resulting in options split into names in seperate rows. And I used ROW_NUMBER() to add value row, If you want to order result set by name, please use ROW_NUMBER() OVER (ORDER BY t.column1), that should and probably will make results look consistent all the time.

Result:

id  name    value
-----------------
101 Apple       1
101 Banana      2
102 Audi        1
102 Mercedes    2
102 Volkswagen  3

Answer 3

You could convert your string to XML and then parse the string to transpose it to rows something like this:

SELECT A.[id]
      ,Split.a.value('.', 'VARCHAR(100)') AS Name
      ,ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY (SELECT NULL)) as Value 
FROM (
    SELECT [id]
        ,CAST('<M>' + REPLACE([options], ',', '</M><M>') + '</M>' AS XML) AS Name
    FROM optionstable
    ) AS A
CROSS APPLY Name.nodes('/M') AS Split(a);

Credits: @SRIRAM

SQL Fiddle Demo