SQL Group By with a condition

Scenario: I need to create a report for the auditors for an ASP.Net application. I have a program that parses the XML of the web.config files in a directory for the authorization tag and creates a report providing which users have access to whic开发者_运维百科h folder in the site structure. The report is shown below.

UserName, Firstname, LastName, Directory, Roles, Access, LastLoginDate

Problem: As you can see from the report some directories (the middle column with GISMO in it) show up twice, with both allow and deny for a user. I am wondering if there is a way to group the results in such a way that if there is a row that has allow for a directory then the deny's are not shown but otherwise they are.

Alternatively if this can be manipulated in VB.net/C# that is also an option. It comes back there and is pumped into an Excel spreadsheet.

Any help is appreciated. Thanks in advance.

Edit: I should have explained better. I still need the deny rows to show if the user isn't allowed in the directory. But if they are allowed then there is no point showing the deny rows.

This works on an Oracle database, so it should work or get you close on SQL Server as I know SQL Server supports the main component of this, the CASE operation.

CREATE TABLE user_permissions (
     user_role   VARCHAR2(10) NOT NULL,
     dir         VARCHAR2(10) NOT NULL,
     user_access VARCHAR2(5)  NOT NULL
);

INSERT INTO user_permissions VALUES ('admin', 'dir1', 'allow');
INSERT INTO user_permissions VALUES ('admin', 'dir2', 'allow');
INSERT INTO user_permissions VALUES ('power', 'dir1', 'allow'); -- Allow and Deny dir1
INSERT INTO user_permissions VALUES ('power', 'dir1', 'deny');
INSERT INTO user_permissions VALUES ('power', 'dir2', 'deny');
COMMIT;


SELECT UNIQUE j.*
FROM (
       SELECT user_role, dir,
              MAX(CASE user_access WHEN 'allow' THEN 1 ELSE 0 END) allowFlag,
              MAX(CASE user_access WHEN 'deny'  THEN 1 ELSE 0 END) denyFlag
       FROM user_permissions
       GROUP BY user_role, dir
     ) t
JOIN user_permissions j ON (t.user_role = j.user_role AND t.dir = j.dir)
WHERE j.user_access = 'allow' OR (t.allowFlag = 0 and user_access = 'deny');

Results:

USER_ROLE  DIR        USER_ACCESS 
---------- ---------- ----------- 
admin      dir1       allow       
admin      dir2       allow       
power      dir1       allow       
power      dir2       deny        

Basically, you use a pivot table to aggregate the multiple rows into a single row describing the attributes for the directory. Once you have the aggregated row, it's easy to compare the attributes you've declared to join up the rows that you want to display.

If you've got SQL Server 2005 or newer, you can use this:

with cte as (
    select ROW_NUMBER() OVER (partition by username, directory order by access) as row, *
    from report
)
select *
from cte
where row = 1

In the partition clause, put in whatever makes a "group" unique.

Reference: http://msdn.microsoft.com/en-us/library/ms190766.aspx

http://msdn.microsoft.com/en-us/library/ms186734.aspx

Something like this should work but this assumes that your paths were not entered such as /directory/directory/, /directory/Directory/, directory/directory/default.aspx, etc. Your best bet would be to parse the data and remove duplicates at the .NET process level you've created, since parsing at that stage is usually easier.

select derived.*,
       case when exists 
         (select top 1 1 from table_name as t2 where t2.username = derived.username and t2.directory=derived.directory and t2.access = 'allow') then 1 else 0 end as is_allowed,
       case when exists 
         (select top 1 1 from table_name as t2 where t2.username = derived.username and t2.directory=derived.directory and t2.access = 'deny') then 1 else 0 end as is_denied,
from
(
 select distinct t.username, t.firstname, t.lastname, t.directory
  from table_name as t
) as derived

SELECT UserName, Firstname, LastName, Directory, Roles, Access, LastLoginDate 
FROM Report R
WHERE Access = 'allow'
   OR ( Access = 'deny' 
      AND NOT EXISTS
         ( SELECT *
           FROM Report R2
           WHERE R2.Directory = R.Directory
             AND R2.UserName = R.UserName 
             AND R2.Roles = R.Roles
         )
      )

Based on your comments, this line should be removed, so only (UserName, Directory) combination is checked:

             AND R2.Roles = R.Roles