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