SQL Query Help.....

• sqldba20

  Ten Centuries

  Points: 1308

  More actions

  November 8, 2012 at 11:53 am

  #269983

  Folks:

  I need help with a SQL:

  CREATE TABLE #tblGroupMaster (GroupName VARCHAR(500))

  INSERT INTO #tblGroupMaster values ('KMV')

  INSERT INTO #tblGroupMaster values ('PPD')

  INSERT INTO #tblGroupMaster values ('SharePoint')

  INSERT INTO #tblGroupMaster values ('CRM')

  INSERT INTO #tblGroupMaster values ('ADMIN')

  INSERT INTO #tblGroupMaster values ('SQLServer')

  CREATE TABLE #tblGroupInformation (AccountName VARCHAR(200), GroupName VARCHAR(500), Type VARCHAR(20))

  INSERT INTO #tblGroupInformation values ('James','KMV','User')

  INSERT INTO #tblGroupInformation values ('Jacob','KMV','User')

  INSERT INTO #tblGroupInformation values ('ADMIN','KMV','Group')

  INSERT INTO #tblGroupInformation values ('Tom','KMV','User')

  INSERT INTO #tblGroupInformation values ('David','PPD','User')

  INSERT INTO #tblGroupInformation values ('Chris','PPD','User')

  INSERT INTO #tblGroupInformation values ('CRM','PPD','Group')

  INSERT INTO #tblGroupInformation values ('Tom','SharePoint','User')

  INSERT INTO #tblGroupInformation values ('CRM','SharePoint','Group')

  INSERT INTO #tblGroupInformation values ('James','SharePoint','User')

  INSERT INTO #tblGroupInformation values ('Tom','CRM','User')

  INSERT INTO #tblGroupInformation values ('SQLServer','CRM','Group')

  INSERT INTO #tblGroupInformation values ('Kelly','ADMIN','User')

  INSERT INTO #tblGroupInformation values ('Joe','ADMIN','User')

  INSERT INTO #tblGroupInformation values ('Harry','ADMIN','User')

  INSERT INTO #tblGroupInformation values ('Bill','SQLServer','User')

  INSERT INTO #tblGroupInformation values ('Jack','SQLServer','User')

  The #tblGroupMaster has distinct list of the all Groups and the #tblGroupInformation has information on who (User / Group) has access to which Group.

  I need to create report on finding which users belong to which group but before that I need to build a SQLScript / SP. The difficult part is a group within a group and then finding the users of that group. The output should be something like in the attachment:

  Thanks !

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 8, 2012 at 12:39 pm

  #1557334

  This might help you, however, you need to understand what is doing before implementing it.

  It was great that you posted DDL and sample data.;-)

  ;WITH rCTE AS(

  SELECT 1 AS level,

  AccountName,

  GroupName,

  GroupName AS MasterGroupName,

  Type

  FROM #tblGroupInformation

  UNION ALL

  SELECT level + 1,

  GI.AccountName,

  GI.GroupName,

  rCTE.MasterGroupName,

  GI.Type

  FROM rCTE

  JOIN #tblGroupInformation GI ON rCTE.AccountName = GI.GroupName

  AND rCTE.Type = 'Group'

  )

  SELECT MasterGroupName,

  CASE WHEN level = 1 THEN AccountName

  WHEN level = 2 THEN GroupName END UsersLevel1,

  CASE WHEN level = 2 THEN AccountName

  WHEN level = 3 THEN GroupName END UsersLevel2,

  CASE WHEN level = 3 THEN AccountName END UsersLevel3

  FROM rCTE

  WHERE type <> 'Group'

  ORDER BY MasterGroupName, UsersLevel1, UsersLevel2, UsersLevel3

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2