| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-09-09 : 18:32:30
|
| One of the FAQs on the SQL Server newsgroups concerns creating a pivot table using T-SQL. A previous article, Dynamic Cross-Tabs/Pivot Tables, covered some more advanced ways to create a pivot table, but I want to show a simpler solution that I have used on one of my current projects. Article Link. |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-11-05 : 23:57:27
|
I'm going to dredge this one up more than a year later.I found this article helpful, however I find that while the data is more clearly presented, performance (in my application) is actually worse than issuing a bunch of individual queries.My application is a logfile that I want to show per-hour stats on, very much like this article uses.My schema is thus (simplified, but all that's relevant to this):CREATE TABLE log (i int identity, i_users int, datetime datetime, i_servers tinyint, i_sites tinyint)CREATE INDEX [idx_log_run_datesite] ON [dbo].[log] ([i_sites], [datetime] desc )CREATE UNIQUE INDEX [PK_log] ON [dbo].[log] ([i])CREATE CLUSTERED INDEX [idx_log_iusers] ON [dbo].[log] ([i_users]) ...My crosstab query is this:SELECT i_servers, SUM(CASE WHEN DATEPART(hour,datetime) = 0 THEN 1 ELSE 0 END) AS '0', SUM(CASE WHEN DATEPART(hour,datetime) = 1 THEN 1 ELSE 0 END) AS '1', SUM(CASE WHEN DATEPART(hour,datetime) = 2 THEN 1 ELSE 0 END) AS '2', SUM(CASE WHEN DATEPART(hour,datetime) = 3 THEN 1 ELSE 0 END) AS '3', SUM(CASE WHEN DATEPART(hour,datetime) = 4 THEN 1 ELSE 0 END) AS '4', SUM(CASE WHEN DATEPART(hour,datetime) = 5 THEN 1 ELSE 0 END) AS '5', SUM(CASE WHEN DATEPART(hour,datetime) = 6 THEN 1 ELSE 0 END) AS '6', SUM(CASE WHEN DATEPART(hour,datetime) = 7 THEN 1 ELSE 0 END) AS '7', SUM(CASE WHEN DATEPART(hour,datetime) = 8 THEN 1 ELSE 0 END) AS '8', SUM(CASE WHEN DATEPART(hour,datetime) = 9 THEN 1 ELSE 0 END) AS '9', SUM(CASE WHEN DATEPART(hour,datetime) = 10 THEN 1 ELSE 0 END) AS '10', SUM(CASE WHEN DATEPART(hour,datetime) = 11 THEN 1 ELSE 0 END) AS '11', SUM(CASE WHEN DATEPART(hour,datetime) = 12 THEN 1 ELSE 0 END) AS '12', SUM(CASE WHEN DATEPART(hour,datetime) = 13 THEN 1 ELSE 0 END) AS '13', SUM(CASE WHEN DATEPART(hour,datetime) = 14 THEN 1 ELSE 0 END) AS '14', SUM(CASE WHEN DATEPART(hour,datetime) = 15 THEN 1 ELSE 0 END) AS '15', SUM(CASE WHEN DATEPART(hour,datetime) = 16 THEN 1 ELSE 0 END) AS '16', SUM(CASE WHEN DATEPART(hour,datetime) = 17 THEN 1 ELSE 0 END) AS '17', SUM(CASE WHEN DATEPART(hour,datetime) = 18 THEN 1 ELSE 0 END) AS '18', SUM(CASE WHEN DATEPART(hour,datetime) = 19 THEN 1 ELSE 0 END) AS '19', SUM(CASE WHEN DATEPART(hour,datetime) = 20 THEN 1 ELSE 0 END) AS '20', SUM(CASE WHEN DATEPART(hour,datetime) = 21 THEN 1 ELSE 0 END) AS '21', SUM(CASE WHEN DATEPART(hour,datetime) = 22 THEN 1 ELSE 0 END) AS '22', SUM(CASE WHEN DATEPART(hour,datetime) = 23 THEN 1 ELSE 0 END) AS '23'FROM log WITH(NOLOCK)WHERE DATETIME>=dateadd(hour,-24,getdate()) and i_sites=1group by i_servers Performance isn't completely miserable, but it's pretty bad. Can I improve things with, say, an index on a computed column based on datepart(hour,datetime)? Any other suggestions?Thanks-bIn case it's useful, here's the showplan_all from that query:SELECT i_servers, SUM(CASE WHEN DATEPART(hour,datetime) = 0 THEN 1 ELSE 0 END) AS '0', SUM(CASE WHEN DATEPART(hour,datetime) = 1 THEN 1 ELSE 0 END) AS '1', SUM(CASE WHEN DATEPART(hour,datetime) = 2 THEN 1 ELSE 0 END) AS '2', SUM(CASE WHEN DATEPART(hour,datetime) = 3 THEN 1 ELSE 0 END) AS '3', SUM(CASE WHEN DATEPART(hour,datetime) = 4 THEN 1 ELSE 0 END) AS '4', SUM(CASE WHEN DATEPART(hour,datetime) = 5 THEN 1 ELSE 0 END) AS '5', SUM(CASE WHEN DATEPART(hour,datetime) = 6 THEN 1 ELSE 0 END) AS '6', SUM(CASE WHEN DATEPART(hour,datetime) = 7 THEN 1 ELSE 0 END) AS '7', SUM(CASE WHEN DATEPART(hour,datetime) = 8 THEN 1 ELSE 0 END) AS '8', SUM(CASE WHEN DATEPART(hour,datetime) = 9 THEN 1 ELSE 0 END) AS '9', SUM(CASE WHEN DATEPART(hour,datetime) = 10 THEN 1 ELSE 0 END) AS '10', SUM(CASE WHEN DATEPART(hour,datetime) = 11 THEN 1 ELSE 0 END) AS '11', SUM(CASE WHEN DATEPART(hour,datetime) = 12 THEN 1 ELSE 0 END) AS '12', SUM(CASE WHEN DATEPART(hour,datetime) = 13 THEN 1 ELSE 0 END) AS '13', SUM(CASE WHEN DATEPART(hour,datetime) = 14 THEN 1 ELSE 0 END) AS '14', SUM(CASE WHEN DATEPART(hour,datetime) = 15 THEN 1 ELSE 0 END) AS '15', SUM(CASE WHEN DATEPART(hour,datetime) = 16 THEN 1 ELSE 0 END) AS '16', SUM(CASE WHEN DATEPART(hour,datetime) = 17 THEN 1 ELSE 0 END) AS '17', SUM(CASE WHEN DATEPART(hour,datetime) = 18 THEN 1 ELSE 0 END) AS '18', SUM(CASE WHEN DATEPART(hour,datetime) = 19 THEN 1 ELSE 0 END) AS '19', SUM(CASE WHEN DATEPART(hour,datetime) = 20 THEN 1 ELSE 0 END) AS '20', SUM(CASE WHEN DATEPART(hour,datetime) = 21 THEN 1 ELSE 0 END) AS '21', SUM(CASE WHEN DATEPART(hour,datetime) = 22 THEN 1 ELSE 0 END) AS '22', SUM(CASE WHEN DATEPART(hour,datetime) = 23 THEN 1 ELSE 0 END) AS '23' FROM log WITH(NOLOCK) WHERE DATETIME>=dateadd(hour,-24,getdate()) and i_sites=1 group by i_servers 1 1 0 NULL NULL 1 NULL 6.1503868 NULL NULL NULL 39.658604 NULL NULL SELECT 0 NULL |--Stream Aggregate(GROUP BY:([log].[i_Servers]) DEFINE:([Expr1002]=SUM([partialagg1028]), [Expr1003]=SUM([partialagg1029]), [Expr1004]=SUM([partialagg1030]), [Expr1005]=SUM([partialagg1031]), [Expr1006]=SUM([partialagg1032]), [Expr1007]=SUM([partialagg1033]), [Expr1008]=SUM([partialagg1034]), [Expr1009]=SUM([partialagg1035]), [Expr1010]=SUM([partialagg1036]), [Expr1011]=SUM([partialagg1037]), [Expr1012]=SUM([partialagg1038]), [Expr1013]=SUM([partialagg1039]), [Expr1014]=SUM([partialagg1040]), [Expr1015]=SUM([partialagg1041]), [Expr1016]=SUM([partialagg1042]), [Expr1017]=SUM([partialagg1043]), [Expr1018]=SUM([partialagg1044]), [Expr1019]=SUM([partialagg1045]), [Expr1020]=SUM([partialagg1046]), [Expr1021]=SUM([partialagg1047]), [Expr1022]=SUM([partialagg1048]), [Expr1023]=SUM([partialagg1049]), [Expr1024]=SUM([partialagg1050]), [Expr1025]=SUM([partialagg1051]))) 1 2 1 Stream Aggregate Aggregate GROUP BY:([log].[i_Servers]) [Expr1002]=SUM([partialagg1028]), [Expr1003]=SUM([partialagg1029]), [Expr1004]=SUM([partialagg1030]), [Expr1005]=SUM([partialagg1031]), [Expr1006]=SUM([partialagg1032]), [Expr1007]=SUM([partialagg1033]), [Expr1008]=SUM([partialagg1034]), [Expr1009]=SUM([partialagg1035]), [Expr1010]=SUM([partialagg1036]), [Expr1011]=SUM([partialagg1037]), [Expr1012]=SUM([partialagg1038]), [Expr1013]=SUM([partialagg1039]), [Expr1014]=SUM([partialagg1040]), [Expr1015]=SUM([partialagg1041]), [Expr1016]=SUM([partialagg1042]), [Expr1017]=SUM([partialagg1043]), [Expr1018]=SUM([partialagg1044]), [Expr1019]=SUM([partialagg1045]), [Expr1020]=SUM([partialagg1046]), [Expr1021]=SUM([partialagg1047]), [Expr1022]=SUM([partialagg1048]), [Expr1023]=SUM([partialagg1049]), [Expr1024]=SUM([partialagg1050]), [Expr1025]=SUM([partialagg1051]) 6.1503868 0.0 9.7483629E-5 110 39.658604 [log].[i_servers], [Expr1002], [Expr1003], [Expr1004], [Expr1005], [Expr1006], [Expr1007], [Expr1008], [Expr1009], [Expr1010], [Expr1011], [Expr1012], [Expr1013], [Expr1014], [Expr1015], [Expr1016], [Expr1017], [Expr1018], [Expr1019], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [Expr1024], [Expr1025] NULL PLAN_ROW 0 1.0 |--Sort(ORDER BY:([log].[i_servers] ASC)) 1 3 2 Sort Sort ORDER BY:([log].[i_servers] ASC) NULL 24.601547 1.1261261E-2 2.7744425E-4 110 39.658508 [log].[i_servers], [partialagg1028], [partialagg1029], [partialagg1030], [partialagg1031], [partialagg1032], [partialagg1033], [partialagg1034], [partialagg1035], [partialagg1036], [partialagg1037], [partialagg1038], [partialagg1039], [partialagg1040], [partialagg1041], [partialagg1042], [partialagg1043], [partialagg1044], [partialagg1045], [partialagg1046], [partialagg1047], [partialagg1048], [partialagg1049], [partialagg1050], [partialagg1051] NULL PLAN_ROW 0 1.0 |--Parallelism(Gather Streams) 1 4 3 Parallelism Gather Streams NULL NULL 24.601547 0.0 2.8999325E-2 110 39.646969 [log].[i_servers], [partialagg1028], [partialagg1029], [partialagg1030], [partialagg1031], [partialagg1032], [partialagg1033], [partialagg1034], [partialagg1035], [partialagg1036], [partialagg1037], [partialagg1038], [partialagg1039], [partialagg1040], [partialagg1041], [partialagg1042], [partialagg1043], [partialagg1044], [partialagg1045], [partialagg1046], [partialagg1047], [partialagg1048], [partialagg1049], [partialagg1050], [partialagg1051] NULL PLAN_ROW -1 1.0 |--Hash Match(Partial Aggregate, HASH:([log].[i_servers]) DEFINE:([partialagg1028]=SUM(If (datepart(hour, Convert([log].[datetime]))=0) then 1 else 0), [partialagg1029]=SUM(If (datepart(hour, Convert([log].[datetime]))=1) then 1 else 0), [partialagg1030]=SUM(If (datepart(hour, Convert([log].[datetime]))=2) then 1 else 0), [partialagg1031]=SUM(If (datepart(hour, Convert([log].[datetime]))=3) then 1 else 0), [partialagg1032]=SUM(If (datepart(hour, Convert([log].[datetime]))=4) then 1 else 0), [partialagg1033]=SUM(If (datepart(hour, Convert([log].[datetime]))=5) then 1 else 0), [partialagg1034]=SUM(If (datepart(hour, Convert([log].[datetime]))=6) then 1 else 0), [partialagg1035]=SUM(If (datepart(hour, Convert([log].[datetime]))=7) then 1 else 0), [partialagg1036]=SUM(If (datepart(hour, Convert([log].[datetime]))=8) then 1 else 0), [partialagg1037]=SUM(If (datepart(hour, Convert([log].[datetime]))=9) then 1 else 0), [partialagg1038]=SUM(If (datepart(hour, Convert([log].[datetime]))=10) then 1 else 0), [partialagg1039]=SUM(If (datepart(hour, Convert([log].[datetime]))=11) then 1 else 0), [partialagg1040]=SUM(If (datepart(hour, Convert([log].[datetime]))=12) then 1 else 0), [partialagg1041]=SUM(If (datepart(hour, Convert([log].[datetime]))=13) then 1 else 0), [partialagg1042]=SUM(If (datepart(hour, Convert([log].[datetime]))=14) then 1 else 0), [partialagg1043]=SUM(If (datepart(hour, Convert([log].[datetime]))=15) then 1 else 0), [partialagg1044]=SUM(If (datepart(hour, Convert([log].[datetime]))=16) then 1 else 0), [partialagg1045]=SUM(If (datepart(hour, Convert([log].[datetime]))=17) then 1 else 0), [partialagg1046]=SUM(If (datepart(hour, Convert([log].[datetime]))=18) then 1 else 0), [partialagg1047]=SUM(If (datepart(hour, Convert([log].[datetime]))=19) then 1 else 0), [partialagg1048]=SUM(If (datepart(hour, Convert([log].[datetime]))=20) then 1 else 0), [partialagg1049]=SUM(If (datepart(hour, Convert([log].[datetime]))=21) then 1 else 0), [partialagg1050]=SUM(If (datepart(hour, Convert([log].[datetime]))=22) then 1 else 0), [partialagg1051]=SUM(If (datepart(hour, Convert([log].[datetime]))=23) then 1 else 0))) 1 5 4 Hash Match Partial Aggregate HASH:([log].[i_servers]), DEFINE:([partialagg1028]=SUM(If (datepart(hour, Convert([log].[datetime]))=0) then 1 else 0), [partialagg1029]=SUM(If (datepart(hour, Convert([log].[datetime]))=1) then 1 else 0), [partialagg1030]=SUM(If (datepart(hour, Convert([log].[datetime]))=2) then 1 else 0), [partialagg1031]=SUM(If (datepart(hour, Convert([log].[datetime]))=3) then 1 else 0), [partialagg1032]=SUM(If (datepart(hour, Convert([log].[datetime]))=4) then 1 else 0), [partialagg1033]=SUM(If (datepart(hour, Convert([log].[datetime]))=5) then 1 else 0), [partialagg1034]=SUM(If (datepart(hour, Convert([log].[datetime]))=6) then 1 else 0), [partialagg1035]=SUM(If (datepart(hour, Convert([log].[datetime]))=7) then 1 else 0), [partialagg1036]=SUM(If (datepart(hour, Convert([log].[datetime]))=8) then 1 else 0), [partialagg1037]=SUM(If (datepart(hour, Convert([log].[datetime]))=9) then 1 else 0), [partialagg1038]=SUM(If (datepart(hour, Convert([log].[datetime]))=10) then 1 else 0), [partialagg1039]=SUM(If (datepart(hour, Convert([log].[datetime]))=11) then 1 else 0), [partialagg1040]=SUM(If (datepart(hour, Convert([log].[datetime]))=12) then 1 else 0), [partialagg1041]=SUM(If (datepart(hour, Convert([log].[datetime]))=13) then 1 else 0), [partialagg1042]=SUM(If (datepart(hour, Convert([log].[datetime]))=14) then 1 else 0), [partialagg1043]=SUM(If (datepart(hour, Convert([log].[datetime]))=15) then 1 else 0), [partialagg1044]=SUM(If (datepart(hour, Convert([log].[datetime]))=16) then 1 else 0), [partialagg1045]=SUM(If (datepart(hour, Convert([log].[datetime]))=17) then 1 else 0), [partialagg1046]=SUM(If (datepart(hour, Convert([log].[datetime]))=18) then 1 else 0), [partialagg1047]=SUM(If (datepart(hour, Convert([log].[datetime]))=19) then 1 else 0), [partialagg1048]=SUM(If (datepart(hour, Convert([log].[datetime]))=20) then 1 else 0), [partialagg1049]=SUM(If (datepart(hour, Convert([log].[datetime]))=21) then 1 else 0), [partialagg1050]=SUM(If (datepart(hour, Convert([log].[datetime]))=22) then 1 else 0), [partialagg1051]=SUM(If (datepart(hour, Convert([log].[datetime]))=23) then 1 else 0)) [partialagg1028]=SUM(If (datepart(hour, Convert([log].[datetime]))=0) then 1 else 0), [partialagg1029]=SUM(If (datepart(hour, Convert([log].[datetime]))=1) then 1 else 0), [partialagg1030]=SUM(If (datepart(hour, Convert([log].[datetime]))=2) then 1 else 0), [partialagg1031]=SUM(If (datepart(hour, Convert([log].[datetime]))=3) then 1 else 0), [partialagg1032]=SUM(If (datepart(hour, Convert([log].[datetime]))=4) then 1 else 0), [partialagg1033]=SUM(If (datepart(hour, Convert([log].[datetime]))=5) then 1 else 0), [partialagg1034]=SUM(If (datepart(hour, Convert([log].[datetime]))=6) then 1 else 0), [partialagg1035]=SUM(If (datepart(hour, Convert([log].[datetime]))=7) then 1 else 0), [partialagg1036]=SUM(If (datepart(hour, Convert([log].[datetime]))=8) then 1 else 0), [partialagg1037]=SUM(If (datepart(hour, Convert([log].[datetime]))=9) then 1 else 0), [partialagg1038]=SUM(If (datepart(hour, Convert([log].[datetime]))=10) then 1 else 0), [partialagg1039]=SUM(If (datepart(hour, Convert([log].[datetime]))=11) then 1 else 0), [partialagg1040]=SUM(If (datepart(hour, Convert([log].[datetime]))=12) then 1 else 0), [partialagg1041]=SUM(If (datepart(hour, Convert([log].[datetime]))=13) then 1 else 0), [partialagg1042]=SUM(If (datepart(hour, Convert([log].[datetime]))=14) then 1 else 0), [partialagg1043]=SUM(If (datepart(hour, Convert([log].[datetime]))=15) then 1 else 0), [partialagg1044]=SUM(If (datepart(hour, Convert([log].[datetime]))=16) then 1 else 0), [partialagg1045]=SUM(If (datepart(hour, Convert([log].[datetime]))=17) then 1 else 0), [partialagg1046]=SUM(If (datepart(hour, Convert([log].[datetime]))=18) then 1 else 0), [partialagg1047]=SUM(If (datepart(hour, Convert([log].[datetime]))=19) then 1 else 0), [partialagg1048]=SUM(If (datepart(hour, Convert([log].[datetime]))=20) then 1 else 0), [partialagg1049]=SUM(If (datepart(hour, Convert([log].[datetime]))=21) then 1 else 0), [partialagg1050]=SUM(If (datepart(hour, Convert([log].[datetime]))=22) then 1 else 0), [partialagg1051]=SUM(If (datepart(hour, Convert([log].[datetime]))=23) then 1 else 0) 24.601547 0.0 2.2327244 110 39.61797 [log].[i_servers], [partialagg1028], [partialagg1029], [partialagg1030], [partialagg1031], [partialagg1032], [partialagg1033], [partialagg1034], [partialagg1035], [partialagg1036], [partialagg1037], [partialagg1038], [partialagg1039], [partialagg1040], [partialagg1041], [partialagg1042], [partialagg1043], [partialagg1044], [partialagg1045], [partialagg1046], [partialagg1047], [partialagg1048], [partialagg1049], [partialagg1050], [partialagg1051] NULL PLAN_ROW -1 1.0 |--Clustered Index Scan(OBJECT:([database].[dbo].[log].[idx_log_iusers]), WHERE:([log].[i_sites]=1 AND Convert([log].[datetime])>=dateadd(hour, -24, getdate()))) 1 6 5 Clustered Index Scan Clustered Index Scan OBJECT:([database].[dbo].[log].[idx_log_iusers]), WHERE:([log].[i_sites]=1 AND Convert([log].[datetime])>=dateadd(hour, -24, getdate())) [log].[i_sites], [log].[datetime], [log].[i_servers] 968224.06 33.929432 1.8276123 75 35.757042 [log].[i_sites], [log].[datetime], [log].[i_servers] NULL PLAN_ROW -1 1.0 |
 |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-11-06 : 00:03:57
|
| (I edited the previous post to remove the CODE tag. That may not be more readable but all the rest of the posts are)-graz===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-11-13 : 14:24:38
|
| To increase performance:Remember that DATEPART is evaluated over and over. You may get better performance by creating a subquery returning your data, grouped by Hour in a field called 'Hour'. Then, do a cross-tab using that field. I do this all the time for Utilization reports here at the office, where we list employees as rows and different types of labor as columns and hours as values.BTW -- this also makes your query shorter and easier to edit and read.SELECT Server, SUM(CASE WHEN Hr = 1 THEN Value ELSE 0 END) as '01', SUM(CASE WHEN Hr = 2 then Value ELSE 0 END) as '02', ..etc...FROM(SELECT server, DatePart(hour,datetime) Hr, SUM(1) as value FROMlogGROUP BY server, DatePart(hour,datetime)) AI have had good luck really increasing query performance by using subqueries this way. |
|
|
|
|
|
|
Article: Counting Transactions per Hour using a Pivot Table