November 18, 2013 at 12:01 pm
Sean Lange (11/18/2013)While the pivot seems a bit contrived here it does a good job of demonstrating how it can be used. ...
The question does test your knowledge of how PIVOT works. ...
I disagree that this does a good job of demonstrating how PIVOT can be used.
I have been trying to run different subsets of the query and I can't tell how the PIVOT does what it does in this situation. (Not to mention why we should do it this way...)
November 18, 2013 at 1:38 pm
timwell (11/18/2013)
Sean Lange (11/18/2013)While the pivot seems a bit contrived here it does a good job of demonstrating how it can be used. ...
The question does test your knowledge of how PIVOT works. ...
I disagree that this does a good job of demonstrating how PIVOT can be used.
I have been trying to run different subsets of the query and I can't tell how the PIVOT does what it does in this situation. (Not to mention why we should do it this way...)
I wouldn't code it like that to retrieve those results. There is no need for a pivot there at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 18, 2013 at 3:01 pm
I think I will start on a QotD demonstrating pivot at its (hopefully) best.
November 18, 2013 at 3:08 pm
Revenant (11/18/2013)
I think I will start on a QotD demonstrating pivot at its (hopefully) best.
I look forward to it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 18, 2013 at 3:12 pm
Sean Lange (11/18/2013)
Revenant (11/18/2013)
I think I will start on a QotD demonstrating pivot at its (hopefully) best.I look forward to it.
I hope I will not disappoint you, Sean. My biggest pivot to date has 122 columns. 😉
November 18, 2013 at 3:17 pm
Revenant (11/18/2013)
Sean Lange (11/18/2013)
Revenant (11/18/2013)
I think I will start on a QotD demonstrating pivot at its (hopefully) best.I look forward to it.
I hope I will not disappoint you, Sean. My biggest pivot to date has 122 columns. 😉
Certainly won't disappoint me. That is a mighty large pivot.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 18, 2013 at 3:51 pm
Good question, but the formatting could do with some improvement.
November 18, 2013 at 10:50 pm
good QotD.. thanks for sharing
November 19, 2013 at 8:56 am
Please do. I always kind of scratch my head over the PIVOT operator.
Thanks,
Bill
November 20, 2013 at 4:57 pm
I have used pivot to make managing permissions much easier for me. Here is a short example of five tables, three grantees and assemblages of SELECT, INSERT, UPDATE and DELETE. I find it impossible to manage permissions using the normal dump of who has what access on what table. Using pivot, I can make a matrix that shows variances quickly. A problem I have had with pivot is with having to hard code the grantees. I learned to generalize this with the stuff function. At the bottom is a more general solution using stuff function and dyanamic SQL.
-- Define table to hold permissions similar to information_schema.table_privileges;
create table #perms (table_name varchar(30), grantee varchar(30), privilege_type varchar(8))
insert into #perms values('t1', 'g1', 'SELECT');
insert into #perms values('t2', 'g1', 'SELECT');
insert into #perms values('t3', 'g1', 'SELECT');
insert into #perms values('t4', 'g1', 'SELECT');
insert into #perms values('t5', 'g1', 'SELECT');
insert into #perms values('t1', 'g2', 'SELECT');
insert into #perms values('t2', 'g2', 'SELECT');
insert into #perms values('t3', 'g2', 'SELECT');
insert into #perms values('t4', 'g2', 'SELECT');
insert into #perms values('t5', 'g2', 'SELECT');
insert into #perms values('t1', 'g2', 'INSERT');
insert into #perms values('t2', 'g2', 'INSERT');
insert into #perms values('t3', 'g2', 'INSERT');
insert into #perms values('t4', 'g2', 'INSERT');
insert into #perms values('t5', 'g2', 'INSERT');
insert into #perms values('t1', 'g2', 'UPDATE');
insert into #perms values('t2', 'g2', 'UPDATE');
insert into #perms values('t3', 'g2', 'UPDATE');
insert into #perms values('t4', 'g2', 'UPDATE');
insert into #perms values('t5', 'g2', 'UPDATE');
insert into #perms values('t1', 'g2', 'DELETE');
insert into #perms values('t2', 'g2', 'DELETE');
insert into #perms values('t3', 'g2', 'DELETE');
insert into #perms values('t4', 'g2', 'DELETE');
insert into #perms values('t5', 'g2', 'DELETE');
insert into #perms values('t3', 'g3', 'SELECT');
insert into #perms values('t4', 'g3', 'SELECT');
insert into #perms values('t5', 'g3', 'SELECT');
insert into #perms values('t2', 'g3', 'INSERT');
insert into #perms values('t4', 'g3', 'INSERT');
insert into #perms values('t5', 'g3', 'INSERT');
insert into #perms values('t2', 'g3', 'UPDATE');
insert into #perms values('t3', 'g3', 'UPDATE');
insert into #perms values('t5', 'g3', 'UPDATE');
insert into #perms values('t2', 'g3', 'DELETE');
insert into #perms values('t3', 'g3', 'DELETE');
insert into #perms values('t4', 'g3', 'DELETE');
-- Show that the data got loaded. See any problems with permissions?
select * from #perms order by table_name, grantee, privilege_type;
-- Show permissions in a matrix. Variances are much easier to spot. It is positional. SIUD in that order.
-- Underscore means that privilege is absent.
select table_name, g1, g2, g3
from (select table_name, grantee, coalesce(s, '_')+coalesce(i, '_')+coalesce(u, '_')+coalesce(d, '_') priv
from (
select table_name, grantee,
max(case when privilege_type = 'SELECT' then 'S' END) as S,
max(case when privilege_type = 'INSERT' then 'I' END) as I,
max(case when privilege_type = 'UPDATE' then 'U' END) as U,
max(case when privilege_type = 'DELETE' then 'D' END) as D
from #perms a
group by table_name, grantee) x) y
pivot(max(priv) for grantee in (g1, g2, g3)) as P
order by table_name;
-- Here is a more general version that gets the grantee names from the data automatically
declare @cols nvarchar(max), @SQL nvarchar(max)
set @cols = stuff((select distinct ', ' + quotename(grantee)
from #perms
for XML path('')), 1, 2,'');
-- select @cols;
set @SQL = 'select table_name, ' + @cols + '
from (select table_name, grantee, isnull(s, ''_'')+isnull(i, ''_'')+isnull(u, ''_'')+isnull(d, ''_'') priv
from (
select table_name, grantee,
max(case when privilege_type = ''SELECT'' then ''S'' END) as S,
max(case when privilege_type = ''INSERT'' then ''I'' END) as I,
max(case when privilege_type = ''UPDATE'' then ''U'' END) as U,
max(case when privilege_type = ''DELETE'' then ''D'' END) as D
from #perms
group by table_name, grantee) x) y
pivot(max(priv) for grantee in (' + @cols + ')) as P
order by table_name;'
execute(@SQL)
November 20, 2013 at 5:07 pm
Tom
Hat off. This is pretty good and goes into my scrapbook.
When we can get together, I am buying.
December 2, 2013 at 7:07 am
Sean Lange (11/18/2013)
Revenant (11/18/2013)
Sean Lange (11/18/2013)
Revenant (11/18/2013)
Sean Lange (11/18/2013)
. . . While the pivot seems a bit contrived here it does a good job of demonstrating how it can be used. It seems to be difficult to find a usage for PIVOT when another methodology is unable to produce the same results more efficiently.I am not sure what is your point, Sean.
Steve, thanks for an interesting QotD!
The question does test your knowledge of how PIVOT works. I was merely trying to say that most of the time when I see PIVOT used there are other ways of getting the same results in a more efficient manner. As a result, I rarely use PIVOT at all in the real world.
select a
from #temp
group by a
ORDER BY CASE WHEN a IS NULL THEN 1 ELSE 0 END
I think it was that 'unable' that threw me off.
LOL. I feel that Jeff's pork chop launcher is aimed at me. I meant "able" not "unable". :blush:
I think you meant 'unable', as in "I'm unable to find a PIVOT example where there isn't another methodology that does the job more efficiently".
December 2, 2013 at 8:02 am
marlon.seton (12/2/2013)
Sean Lange (11/18/2013)
Revenant (11/18/2013)
Sean Lange (11/18/2013)
Revenant (11/18/2013)
Sean Lange (11/18/2013)
. . . While the pivot seems a bit contrived here it does a good job of demonstrating how it can be used. It seems to be difficult to find a usage for PIVOT when another methodology is unable to produce the same results more efficiently.I am not sure what is your point, Sean.
Steve, thanks for an interesting QotD!
The question does test your knowledge of how PIVOT works. I was merely trying to say that most of the time when I see PIVOT used there are other ways of getting the same results in a more efficient manner. As a result, I rarely use PIVOT at all in the real world.
select a
from #temp
group by a
ORDER BY CASE WHEN a IS NULL THEN 1 ELSE 0 END
I think it was that 'unable' that threw me off.
LOL. I feel that Jeff's pork chop launcher is aimed at me. I meant "able" not "unable". :blush:
I think you meant 'unable', as in "I'm unable to find a PIVOT example where there isn't another methodology that does the job more efficiently".
Yeah what he said. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2015 at 1:06 pm
Reading old info on pivots. Some good comments.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply