SQLTeam.com | Weblogs | Forums

Help needed in recursive split


#1

I am using the function dbo.DelimitedSplit8K to split. i neeed to split the result of another split

[code] Declare @UserGroup nvarchar(MAX);

SET @UserGroup = 'UserGroup1|User1|User2|User3&UserGroup2|user4|user5|user6&Usergroup3|user7|user8|user9';

selecT * FROM dbo.DelimitedSplit8K(@UserGroup,'&') [/code]

expected result:

select 'UserGroup1','User1' union all select 'UserGroup1','User2' union all select 'UserGroup1','User3' union all select 'UserGroup2','User4' union all select 'UserGroup2','User5' union all select 'UserGroup2','User6' union all select 'UserGroup3','User7' union all select 'UserGroup3','User8' union all select 'UserGroup3','User9'

Any sugestion how to make the resut like i mentioned above


#2
 Select grp.Item
      , itm.Item
   From dbo.DelimitedSplit8K(@UserGroup, '&') dsk
 Cross Apply (Select * From dbo.DelimitedSplit8K(dsk.Item, '|') a Where a.ItemNumber = 1) As grp
 Cross Apply (Select * From dbo.DelimitedSplit8K(dsk.Item, '|') b Where b.ItemNumber > 1) As itm

Maybe something like this?