Treating multiple delimiter separated fields as distinct rows

Question

I inherited a table (who hasn't, right?) that has data that looks like this:

Item          |             Properties        |        Quantity
--------------------------------------------------------------------
Shirt         |  button-down,polo,sleeveless  |          4,5,8

For the short term, I want to create a view, but eventually I want to export the data to a new version of the table when time allows and have it more like:

Item          |             Properties        |        Quantity
--------------------------------------------------------------------
Shirt         |            button-down        |            4   
Shirt         |               polo            |            5
Shirt         |             sleeveless        |            8

Essentially, take multiple column groups (I imagine there will be other tables where there are more columns than two with this sort of behavior) that are known to be delimiter separated and break them into distinct rows? Any other rows gathered that aren't like this would be shared amongst them like Item in this example. # of commas are uniform between these types.

edit: I used the function given in the answer to How to convert comma separated NVARCHAR to table records in SQL Server 2005? and this is currently what I have:

select distinct data.item, tmptbl.[String] from
  data cross apply [ufn_CSVToTable](data.properties, ',') tmptbl ...

This works in the single column context, but applying that function to a second column (quantity in this case) outright would generate every possible combination of properties and quantities, right? In fact, yes it did result in that when I attempted. It would seem I'd need a cursor or similar to effectively break into individual rows of properties[i] | quantity[i], will try and construct that. That or may just select the data over and split it on the application side.

Answer 1

Using one of the split strings from here..

If you are sure Properties will always have same number of count as quantity (i mean 3 values in properties and 3 values in quantity),then You can replace below join with inner one..

;With cte
as
(select t.item ,a1.item as 'Properties',row_number() over (order by (select null)) as rownum1
 from #test t
cross apply
[dbo].[SplitStrings_Numbers](proper,',') a1
)
,cte1 as
(
select a2.item as quantity,row_number() over (order by (select null)) as rownum2
 from #test t
cross apply
[dbo].[SplitStrings_Numbers](quantity,',') a2
)
Select c.ite,c.Properties,c1.quantity
from cte c
full join
cte1 c1
on c.rownum1=c1.rownum2

Output:

item    Properties  quantity
Shirt   button-down    4
Shirt   polo           5
Shirt   sleeveless     8

Answer 2

With the help of a splitter and cross apply

Declare @YourTable table (item varchar(50),Properties varchar(50),Quantities varchar(50))
Insert into @YourTable values
('Shirt','button-down,polo,sleeveless','4,5,8')


Select A.item
      ,B.Properties
      ,B.Quantities
 From @YourTable A
 Cross Apply (Select Properties=A.Key_Value
                    ,Quantities=B.Key_Value
               From (Select * from [dbo].[udf-Str-Parse](A.Properties,',')) A
               Left Join (Select * from [dbo].[udf-Str-Parse](A.Quantities,',')) B on A.Key_PS=B.Key_PS 
 ) B

Returns

item    Properties    Quantities
Shirt   button-down   4
Shirt   polo          5
Shirt   sleeveless    8

The UDF

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--       Select * from [dbo].[udf-Str-Parse]('id26,id46|id658,id967','|')
--       Select * from [dbo].[udf-Str-Parse]('hello world. It. is. . raining.today','.')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select Key_Value = ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End

Answer 3

CREATE FUNCTION [dbo].[ReturnTableOfVarchars]  
  (@IDList varchar(8000))  
    -- allow up to 256 varchar  
    RETURNS @IDTable table (RecordID varchar(256) NOT NULL)  
AS  
  BEGIN  
    DECLARE @IDListPosition int,   
      @ArrValue varchar(8000)  
    SET @IDList = COALESCE(@IDList,'')  
    IF @IDList<>''  
      BEGIN  
        -- add a comma to end of list  
        SELECT @IDList = @IDList+','  
        -- Loop through the comma delimited string list  
        WHILE PATINDEX('%,%',@IDList)<>0  
        BEGIN  
          -- find the position of the first comma in the list  
          SELECT @IDListPosition = PATINDEX('%,%',@IDList)  
          -- extract the string  
          SELECT @ArrValue = LEFT(@IDList, @IDListPosition - 1)  
          INSERT @IDTable (RecordID) VALUES(@ArrValue)  
          -- remove processed string  
          SELECT @IDList = STUFF(@IDList,1,@IDListPosition ,'')  
        END  
      END  
    RETURN  
  END  

using:

declare @itemvalues varchar(100) ,@itemcount varchar(100)
set @itemvalues='button-down,polo,sleeveless'
SET @itemcount =' 4,5,8'

select * from dbo.ReturnTableOfVarchars(@itemvalues)
select * from dbo.ReturnTableOfVarchars(@itemcount)

The function above splits the values from the string and you can insert the values from the select or update according to your process.