asked
How do I pivot this dataset?
Morning all I am having serious brain freeze this morning! Is it possible to pivot this... LineName ReportDate CallsOffered CallsAns PCA ASA MaxAnsDelay AAB MaxAAB -------------------------------------------------- ----------------------- ------------ ----------- --------------------------------------- ---------------------- ----------- ---------------------- ----------- EBillingHD 2011-07-01 00:00:00 38 36 0.95 30 381 146 206 GPP CEVA 2011-07-01 00:00:00 1 1 1.00 2 2 0 0 GPPHD 2011-07-01 00:00:00 320 310 0.97 13 304 58 252 GPPRegions 2011-07-01 00:00:00 32 32 1.00 25 373 0 0 GPPTransExecOnly 2011-07-01 00:00:00 13 11 0.85 22 188 93 130 GPPHD 2011-07-02 00:00:00 0 0 0.00 0 0 0 0 EBillingHD 2011-07-04 00:00:00 31 30 0.97 8 60 105 105 GPP CEVA 2011-07-04 00:00:00 4 4 1.00 105 405 0 0 GPPHD 2011-07-04 00:00:00 342 337 0.99 15 286 42 141 GPPRegions 2011-07-04 00:00:00 25 25 1.00 31 245 0 0 GPPTransExecOnly 2011-07-04 00:00:00 13 13 1.00 63 474 0 0 EBillingHD 2011-07-05 00:00:00 31 31 1.00 7 56 0 0 GPP CEVA 2011-07-05 00:00:00 7 7 1.00 133 329 0 0 GPPHD 2011-07-05 00:00:00 337 332 0.99 15 385 55 181 GPPRegions 2011-07-05 00:00:00 35 34 0.97 16 240 0 0 GPPTransExecOnly 2011-07-05 00:00:00 19 18 0.95 14 122 33 33 EBillingHD 2011-07-06 00:00:00 35 30 0.86 9 76 37 144 GPP CEVA 2011-07-06 00:00:00 3 3 1.00 2 4 0 0 GPPHD 2011-07-06 00:00:00 334 321 0.96 12 273 75 315 GPPRegions 2011-07-06 00:00:00 34 34 1.00 9 61 0 0 GPPTransExecOnly 2011-07-06 00:00:00 12 12 1.00 11 52 0 0 Into this format? LineName Entity 2011-07-01 2011-07-02 2011-07-03 ... ---------------------------------------------------------------- EBillingHD CallsOffered 38 31 13 ... EBillingHD CallsAnswered 12 34 56 ... ... Sorry about the rushed question I need to be in a meeting. I hope you get the gist from this?
Fortunately, I just created something kind of similar the other day, so I adapted it for these purposes. I didn't include the full data set because I didn't want to type it all out... The beginning part sets up the data set that I did use. /* This is the data setup portion. */ create table report ( LineName varchar(30), ReportDate date, CallsOffered int, CallsAnswered int, PCA decimal(3,2) ) insert into report(LineName, ReportDate, CallsOffered, CallsAnswered, PCA) select 'EBillingHD', '2011-07-01', 38, 36, 0.95 UNION ALL select 'GPP CEVA', '2011-07-01', 1, 1, 1.00 UNION ALL select 'GPPHD', '2011-07-01', 320, 310, 0.97 UNION ALL select 'GPPHD', '2011-07-02', 0, 0, 0.00 UNION ALL select 'EBillingHD', '2011-07-04', 31, 30, 0.97 UNION ALL select 'GPP CEVA', '2011-07-04', 4,4, 1.00 UNION ALL select 'GPPHD', '2011-07-04', 342, 337, 0.99 UNION ALL select 'EBillingHD', '2011-07-05',31, 31, 1.00 UNION ALL select 'GPP CEVA', '2011-07-05', 7, 7, 1.00 UNION ALL select 'GPPHD', '2011-07-05', 337, 332, 0.99 UNION ALL select 'EBillingHD', '2011-07-06', 35, 30, 0.86 UNION ALL select 'GPP CEVA', '2011-07-06', 3, 3, 1.00 UNION ALL select 'GPPHD', '2011-07-06', 334, 321, 0.96; /* End Data setup portion. */ declare @MinDate date; declare @MaxDate date; select @MinDate = MIN(ReportDate) from report; select @MaxDate = MAX(ReportDate) from report; declare @columns table ( ColumnNumber int, Name nvarchar(10) ); declare @Entities table ( EntityNumber int, EntityName nvarchar(20) ); insert into @Entities select 0, 'CallsOffered' UNION ALL select 1, 'CallsAnswered' UNION ALL select 2, 'PCA'; declare @MaxEntityNumber int; select @MaxEntityNumber = MAX(EntityNumber) from @Entities; insert into @columns(ColumnNumber, Name) select t.N, DATEADD(day, t.N, @MinDate) from sp_Tally t where t.N
Added bonus: @Oleg, @AaronBertrand, and @crisgomez have even more answers for how to create dynamic pivots at
http://ask.sqlservercentral.com/questions/75685/please-help-to-solve-this-sql-server-problem. All four of our answers are slightly different, but they're roughly the same idea.
Yup. You'll need to unpivot to get the callsoffered / callsanswered split, and then pivot on date. Dynamic SQL may be the way to go, assuming you don't want to rewrite this query for every month.
