Hi Luis,

thanks for the reply and i would like to put my requirement in detail. please note i have updated my sample on the first post.

select * from @Sample...

This is how i have achieved.

Thanks everyone who tried to help me

Declare @test-2 table(Id int identity(1,1) primary key, Value nvarchar(100) )

insert into @test-2(value)

...

Thank you Eirik. It worked for me. Appreciated

Hi Gail,

Will either one satisfy my need? which one is the best for my situation and if possible could you please post me a sample code to understand.

Hi Chris, thanks for the explanation.

Hi Gail,

Yes i have proper error handling in the catch block. Any more suggestions how to handle this situation.

thank you Erik.

thank you luis. this helped me.

this is what i could think off

Declare @UserGroup nvarchar(MAX);

SET @UserGroup = '|User1|User2|User3^UserGroup2@|user4|user5|user6^Usergroup3@';

selecT case when charindex('|',uc.UserGroup) = 1 then null else uc.UserGroup end, us.Item

FROM dbo.DelimitedSplit8K(@UserGroup,'^') gs

CROSS APPLY...

of course this is weired and i added another identifier to identify the group

Declare @UserGroup nvarchar(MAX);

SET @UserGroup = '|User1|User2|User3^UserGroup2@|user4|user5|user6^Usergroup3@';

selecT uc.UserGroup, us.Item

FROM dbo.DelimitedSplit8K(@UserGroup,'^') gs

CROSS APPLY (SELECT LEFT( gs.Item, CHARINDEX(...

Hi Luiz, one quick question that there might be scenario where group name will not be there like below

SET @UserGroup = 'User1|User2|User3^UserGroup2|user4|user5|user6^Usergroup3';

in that case i want the group name has...

thanks luiz and eric. much appreciated

Hi,

yes, it doesn't match the order. is there any way to avoid the pivoting here.

Thank you champs and i tried with cross apply to and it's working. Nice to learn three ways to achieve this. Appreciated your time.

Thanks a lot guys for your suggestion and solution. Appreciated....

thanks a lot everyone for the inputs