SQL HELP

[Vincento Harris]

I am trying to order user roles by privileges
Would ideally like it to look like this
severole permission
*************** *************** *
sysadmin Kill
restore database
restore log
*************** *************** **
instead of
severrole premission
*************** *************** **
sysadmin restore database
sysadmin restore log
sysadmin kill
*************** *************** **

In oracle break on serverole normally does it ..


BTW your recommendations for Programming in SQL server book(s)
are welcome.(Not very advanced ones)

I appreciate all the help from the google news group

Vince

[Erland Sommarskog]

Re: SQL HELP

[posted and mailed, please reply in news]

Vincento Harris (wumutek@yahoo. com) writes:[color=blue]
> I am trying to order user roles by privileges
> Would ideally like it to look like this
> severole permission
> *************** *************** *
> sysadmin Kill
> restore database
> restore log
> *************** *************** **
> instead of
> severrole premission
> *************** *************** **
> sysadmin restore database
> sysadmin restore log
> sysadmin kill
> *************** *************** **
>
> In oracle break on serverole normally does it ..[/color]

This is thing that is best left to a reporting tool. But you can
achieve this in SQL, although it is a bit messy.

CREATE TABLE #tmp (tmpid int IDENTITY,
serverrole varchar(20) NOT NULL,
permission varchar(20) NOT NULL)

INSERT #tmp (serverrole, permission)
SELECT serverrole, permission
FROM tbl
ORDER BY serverrole, permission
OPTION (MAXDOP 1)

UPDATE a
SET serverrole = ' '
FROM #tmp a
JOIN #tmp b ON a.tmpid = b.tmpid - 1
AND a.serverrole = b.serverrole

SELECT serverrole, permission
FROM #tmp
ORDER BY tmpid

The above is untested. You may to play around a little to get it
working.

The OPTION (MAXDOP 1) is there to prevent parallellism, as parallellism
can cause the identity values not to be the expected ones.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp