Display all the users with group descriptions for semicolon seperated values.

• Bagath

  SSCrazy

  Points: 2409

  More actions

  March 21, 2012 at 8:23 am

  #255167

  Hi all,

  Create table UserTable (C101 varchar(100),C8 Varchar(1000),C104 Varchar(1000))

  GO

  insert into UserTable values('n3222','Test','701;704')

  go

  insert into UserTable values('M444,'Test1','501;503;701')

  GO

  SELECT * FROM UserTable

  GO

  create table group_cache (groupid varchar(1000),groupname varchar(1000))

  GO

  insert into group_cache values('701','ppppprrrg')

  insert into group_cache values('704','CRM')

  insert into group_cache values('501','CRM-T')

  insert into group_cache values('503','CRM-P')

  GO

  SELECT * FROM group_cache

  C104= SET OF Groupids for a user separated with semicolon like '501;503;701' and these groupids description is available in group_cache table as a single record per groupid.

  My requirement is, display all the users with group descriptions which they have the group permission like below.

  C8C104

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

  Testppppprrrg;CRM

  Test1CRM-T;CRM-P;ppppprrrg

  Thank you in advance.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  March 21, 2012 at 8:39 am

  #1462182

  SELECT u.C8,

  (SELECT g.groupname+';' AS "text()"

  FROM group_cache g

  WHERE ';'+u.C104+';' LIKE '%;'+g.groupid+';%'

  ORDER BY g.groupid

  FOR XML PATH('')) AS C104

  FROM UserTable u

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  March 21, 2012 at 8:47 am

  #1462193

  Someone else recently posted the exact same question, so this looks like a homework assignment. I suggest you try it on your own first.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Bagath

  SSCrazy

  Points: 2409

  More actions

  March 21, 2012 at 8:49 am

  #1462196

  That's perfect sir...Thanks a lot for your timely help 🙂