PIVOT data

Prev: Help with DateTimeOffset and Floor/Ceiling?
Next: Error Sorting Strings

From: rgn on 25 May 2010 13:32

---

I'm trying to get the first result pivoted so that the each of the 6 type of
permissions show up as individual columns. But looks like I''m missing
something since there is no real aggregation. Can someone help me?

The Result of the first query looks good but each of the permissions results
in a row which is not what the SOX team want. They want all the permissions
for an object and the user in one row.

Thanks


Select object_name(major_id),
grantee.name,
grantor.name,
CASE P.TYPE
WHEN 'SL' THEN 'Y' ELSE '' END 'SELECT',
CASE P.TYPE
WHEN 'DL' THEN 'Y' ELSE '' END 'DELETE',
CASE P.TYPE
WHEN 'IN' THEN 'Y' ELSE '' END 'INSERT',
CASE P.TYPE
WHEN 'UP' THEN 'Y' ELSE '' END 'UPDATE',
CASE P.TYPE
WHEN 'EX' THEN 'Y' ELSE '' END 'EXECUTE',
CASE P.TYPE
WHEN 'VW' THEN 'Y' ELSE '' END 'VIEW DEFINITION'
from sys.database_permissions P
Inner Join sys.all_objects A
ON A.OBJECT_ID = P.MAJOR_ID
Inner Join sys.database_principals grantee
ON P.grantee_principal_id = grantee.principal_id
Inner Join sys.database_principals grantor
ON P.grantor_principal_id = grantor.principal_id
where major_id > 0
order by 1


SELECT
Object,grantee_name,grantor_name,['SL'],['DL'],['IN'],['UP'],['EX'],['VW']
FROM
(SELECT object_name(major_id) Object,
grantee.name grantee_name,
grantor.name grantor_name,
P.TYPE
from sys.database_permissions P
Inner Join sys.all_objects A
ON A.OBJECT_ID = P.MAJOR_ID
Inner Join sys.database_principals grantee
ON P.grantee_principal_id = grantee.principal_id
Inner Join sys.database_principals grantor
ON P.grantor_principal_id = grantor.principal_id
where major_id > 0) Z
PIVOT
( COUNT(Type)
FOR Type IN (['SL'],['DL'],['IN'],['UP'],['EX'],['VW']) ) AS X
Order by 1

From: rgn on 25 May 2010 13:40

---

Note that I put the aggregate ( COUNT(Type) since I dont know what to
aggregate on since the aggregation is not on numeric data ($$ values, COUNT
numbers or Averages).



"rgn" wrote:

> I'm trying to get the first result pivoted so that the each of the 6 type of
> permissions show up as individual columns. But looks like I''m missing
> something since there is no real aggregation. Can someone help me?
>
> The Result of the first query looks good but each of the permissions results
> in a row which is not what the SOX team want. They want all the permissions
> for an object and the user in one row.
>
> Thanks
>
>
> Select object_name(major_id),
> grantee.name,
> grantor.name,
> CASE P.TYPE
> WHEN 'SL' THEN 'Y' ELSE '' END 'SELECT',
> CASE P.TYPE
> WHEN 'DL' THEN 'Y' ELSE '' END 'DELETE',
> CASE P.TYPE
> WHEN 'IN' THEN 'Y' ELSE '' END 'INSERT',
> CASE P.TYPE
> WHEN 'UP' THEN 'Y' ELSE '' END 'UPDATE',
> CASE P.TYPE
> WHEN 'EX' THEN 'Y' ELSE '' END 'EXECUTE',
> CASE P.TYPE
> WHEN 'VW' THEN 'Y' ELSE '' END 'VIEW DEFINITION'
> from sys.database_permissions P
> Inner Join sys.all_objects A
> ON A.OBJECT_ID = P.MAJOR_ID
> Inner Join sys.database_principals grantee
> ON P.grantee_principal_id = grantee.principal_id
> Inner Join sys.database_principals grantor
> ON P.grantor_principal_id = grantor.principal_id
> where major_id > 0
> order by 1
>
>
> SELECT
> Object,grantee_name,grantor_name,['SL'],['DL'],['IN'],['UP'],['EX'],['VW']
> FROM
> (SELECT object_name(major_id) Object,
> grantee.name grantee_name,
> grantor.name grantor_name,
> P.TYPE
> from sys.database_permissions P
> Inner Join sys.all_objects A
> ON A.OBJECT_ID = P.MAJOR_ID
> Inner Join sys.database_principals grantee
> ON P.grantee_principal_id = grantee.principal_id
> Inner Join sys.database_principals grantor
> ON P.grantor_principal_id = grantor.principal_id
> where major_id > 0) Z
> PIVOT
> ( COUNT(Type)
> FOR Type IN (['SL'],['DL'],['IN'],['UP'],['EX'],['VW']) ) AS X
> Order by 1
>

From: Plamen Ratchev on 25 May 2010 17:25

---

Try this:

SELECT OBJECT_NAME(major_id) AS objectname,
grantee.name,
grantor.name,
MAX(CASE P.TYPE WHEN 'SL' THEN 'Y' ELSE '' END) AS 'SELECT',
MAX(CASE P.TYPE WHEN 'DL' THEN 'Y' ELSE '' END) AS 'DELETE',
MAX(CASE P.TYPE WHEN 'IN' THEN 'Y' ELSE '' END) AS 'INSERT',
MAX(CASE P.TYPE WHEN 'UP' THEN 'Y' ELSE '' END) AS 'UPDATE',
MAX(CASE P.TYPE WHEN 'EX' THEN 'Y' ELSE '' END) AS 'EXECUTE',
MAX(CASE P.TYPE WHEN 'VW' THEN 'Y' ELSE '' END) AS 'VIEW
DEFINITION'
FROM sys.database_permissions AS P
JOIN sys.all_objects AS A
ON A.OBJECT_ID = P.MAJOR_ID
JOIN sys.database_principals AS grantee
ON P.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals AS grantor
ON P.grantor_principal_id = grantor.principal_id
WHERE major_id > 0
GROUP BY OBJECT_NAME(major_id),
grantee.name,
grantor.name
ORDER BY objectname;

--
Plamen Ratchev
http://www.SQLStudio.com

From: rgn on 27 May 2010 16:34

---

Thanks. THat was smart. I never thought of manipulating the result set with
Group By :)

grajee

"Plamen Ratchev" wrote:

> Try this:
>
> SELECT OBJECT_NAME(major_id) AS objectname,
> grantee.name,
> grantor.name,
> MAX(CASE P.TYPE WHEN 'SL' THEN 'Y' ELSE '' END) AS 'SELECT',
> MAX(CASE P.TYPE WHEN 'DL' THEN 'Y' ELSE '' END) AS 'DELETE',
> MAX(CASE P.TYPE WHEN 'IN' THEN 'Y' ELSE '' END) AS 'INSERT',
> MAX(CASE P.TYPE WHEN 'UP' THEN 'Y' ELSE '' END) AS 'UPDATE',
> MAX(CASE P.TYPE WHEN 'EX' THEN 'Y' ELSE '' END) AS 'EXECUTE',
> MAX(CASE P.TYPE WHEN 'VW' THEN 'Y' ELSE '' END) AS 'VIEW
> DEFINITION'
> FROM sys.database_permissions AS P
> JOIN sys.all_objects AS A
> ON A.OBJECT_ID = P.MAJOR_ID
> JOIN sys.database_principals AS grantee
> ON P.grantee_principal_id = grantee.principal_id
> JOIN sys.database_principals AS grantor
> ON P.grantor_principal_id = grantor.principal_id
> WHERE major_id > 0
> GROUP BY OBJECT_NAME(major_id),
> grantee.name,
> grantor.name
> ORDER BY objectname;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>

From: rgn on 27 May 2010 17:19

---

The Quotes was messing it up:

SELECT
Object,grantee_name,grantor_name,[SL],[DL],[IN],[UP],[EX],[VW]
FROM
(SELECT object_name(major_id) Object,
grantee.name grantee_name,
grantor.name grantor_name,
P.TYPE
from sys.database_permissions P
Inner Join sys.all_objects A
ON A.OBJECT_ID = P.MAJOR_ID
Inner Join sys.database_principals grantee
ON P.grantee_principal_id = grantee.principal_id
Inner Join sys.database_principals grantor
ON P.grantor_principal_id = grantor.principal_id
where major_id > 0) Z
PIVOT
( COUNT(Type)
FOR Type IN ([SL],[DL],[IN],[UP],[EX],[VW]) ) AS X
Order by 1


> SELECT
> Object,grantee_name,grantor_name,['SL'],['DL'],['IN'],['UP'],['EX'],['VW']
> FROM
> (SELECT object_name(major_id) Object,
> grantee.name grantee_name,
> grantor.name grantor_name,
> P.TYPE
> from sys.database_permissions P
> Inner Join sys.all_objects A
> ON A.OBJECT_ID = P.MAJOR_ID
> Inner Join sys.database_principals grantee
> ON P.grantee_principal_id = grantee.principal_id
> Inner Join sys.database_principals grantor
> ON P.grantor_principal_id = grantor.principal_id
> where major_id > 0) Z
> PIVOT
> ( COUNT(Type)
> FOR Type IN (['SL'],['DL'],['IN'],['UP'],['EX'],['VW']) ) AS X
> Order by 1





"rgn" wrote:

> I'm trying to get the first result pivoted so that the each of the 6 type of
> permissions show up as individual columns. But looks like I''m missing
> something since there is no real aggregation. Can someone help me?
>
> The Result of the first query looks good but each of the permissions results
> in a row which is not what the SOX team want. They want all the permissions
> for an object and the user in one row.
>
> Thanks
>
>
> Select object_name(major_id),
> grantee.name,
> grantor.name,
> CASE P.TYPE
> WHEN 'SL' THEN 'Y' ELSE '' END 'SELECT',
> CASE P.TYPE
> WHEN 'DL' THEN 'Y' ELSE '' END 'DELETE',
> CASE P.TYPE
> WHEN 'IN' THEN 'Y' ELSE '' END 'INSERT',
> CASE P.TYPE
> WHEN 'UP' THEN 'Y' ELSE '' END 'UPDATE',
> CASE P.TYPE
> WHEN 'EX' THEN 'Y' ELSE '' END 'EXECUTE',
> CASE P.TYPE
> WHEN 'VW' THEN 'Y' ELSE '' END 'VIEW DEFINITION'
> from sys.database_permissions P
> Inner Join sys.all_objects A
> ON A.OBJECT_ID = P.MAJOR_ID
> Inner Join sys.database_principals grantee
> ON P.grantee_principal_id = grantee.principal_id
> Inner Join sys.database_principals grantor
> ON P.grantor_principal_id = grantor.principal_id
> where major_id > 0
> order by 1
>
>
> SELECT
> Object,grantee_name,grantor_name,['SL'],['DL'],['IN'],['UP'],['EX'],['VW']
> FROM
> (SELECT object_name(major_id) Object,
> grantee.name grantee_name,
> grantor.name grantor_name,
> P.TYPE
> from sys.database_permissions P
> Inner Join sys.all_objects A
> ON A.OBJECT_ID = P.MAJOR_ID
> Inner Join sys.database_principals grantee
> ON P.grantee_principal_id = grantee.principal_id
> Inner Join sys.database_principals grantor
> ON P.grantor_principal_id = grantor.principal_id
> where major_id > 0) Z
> PIVOT
> ( COUNT(Type)
> FOR Type IN (['SL'],['DL'],['IN'],['UP'],['EX'],['VW']) ) AS X
> Order by 1
>

 | 
Pages: 1
Prev: Help with DateTimeOffset and Floor/Ceiling?
Next: Error Sorting Strings