Grouping Logic

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  December 12, 2016 at 1:25 pm

  #338159

  Hi,

  Below the sample query to play with

  Declare @UserGroup nvarchar(MAX);

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

  selecT uc.UserGroup, us.Item

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

  CROSS APPLY (SELECT LEFT( gs.Item, CHARINDEX( '|', gs.Item + '|') - 1) AS UserGroup,

  SUBSTRING( gs.Item, CHARINDEX( '|', gs.Item + '|') + 1, 8000) AS Users) uc

  CROSS APPLY dbo.DelimitedSplit8K( uc.Users, '|') us

  I need to add another column(Ordernum) to this result and assign 1 value to each usergroup.

  Expected Result:

  select 'UserGroup1','User1',1 union all

  select 'UserGroup1','User2',1 union all

  select 'UserGroup1','User3',1 union all

  select 'UserGroup2','User4',2 union all

  select 'UserGroup2','User5',2 union all

  select 'UserGroup2','User6',2 union all

  select 'UserGroup3','User7',3 union all

  select 'UserGroup3','User8',3 union all

  select 'UserGroup3','User8',3

  Any suggestions how to acheive this please and sample if any.

  Thank you

• Eirikur Eiriksson

  SSC Guru

  Points: 182901

  More actions

  December 12, 2016 at 1:49 pm

  #1917476

  KGJ-Dev (12/12/2016)

  ---

  Hi,

  Below the sample query to play with

  Declare @UserGroup nvarchar(MAX);

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

  selecT uc.UserGroup, us.Item

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

  CROSS APPLY (SELECT LEFT( gs.Item, CHARINDEX( '|', gs.Item + '|') - 1) AS UserGroup,

  SUBSTRING( gs.Item, CHARINDEX( '|', gs.Item + '|') + 1, 8000) AS Users) uc

  CROSS APPLY dbo.DelimitedSplit8K( uc.Users, '|') us

  I need to add another column(Ordernum) to this result and assign 1 value to each usergroup.

  Expected Result:

  select 'UserGroup1','User1',1 union all

  select 'UserGroup1','User2',1 union all

  select 'UserGroup1','User3',1 union all

  select 'UserGroup2','User4',2 union all

  select 'UserGroup2','User5',2 union all

  select 'UserGroup2','User6',2 union all

  select 'UserGroup3','User7',3 union all

  select 'UserGroup3','User8',3 union all

  select 'UserGroup3','User8',3

  Any suggestions how to acheive this please and sample if any.

  Thank you

  Something simple like this?

  😎

  Declare @UserGroup nvarchar(MAX);

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

  selecT

  uc.UserGroup

  ,us.Item

  ,DENSE_RANK() OVER

  (

  ORDER BY uc.UserGroup

  ) AS DRNK

  FROM dbo.DelimitedSplit8K_LEAD(@UserGroup,'&') gs

  CROSS APPLY (SELECT LEFT( gs.Item, CHARINDEX( '|', gs.Item + '|') - 1) AS UserGroup,

  SUBSTRING( gs.Item, CHARINDEX( '|', gs.Item + '|') + 1, 8000) AS Users) uc

  CROSS APPLY dbo.DelimitedSplit8K_LEAD( uc.Users, '|') us;

  Output

  UserGroup Item DRNK

  ----------- ------ -----

  UserGroup1 User1 1

  UserGroup1 User2 1

  UserGroup1 User3 1

  UserGroup2 user4 2

  UserGroup2 user5 2

  UserGroup2 user6 2

  Usergroup3 user7 3

  Usergroup3 user8 3

  Usergroup3 user9 3

  BTW DelimitedSplit8K_LEAD is my version of DelimitedSplit8K, details here[/url]

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  December 12, 2016 at 2:45 pm

  #1917485

  Thank you Eirik. It worked for me. Appreciated