question

lokapedia avatar image
lokapedia asked

Calculated Pivot table script help

Hello Experts, I am an Uni Student (Still learning SQL) and need your help to write a Pivot table sql script to help in my exam. **Scenario** Company XYZ sells products. Company measures their performance by checking how long does it take to complete the entire order. Each order goes through multiple status (Example : Open, Pending, Close) They count days for each status to know how many days an order was on a particular status. the days are calculated in two different ways, Working Days and Calendar Days (Please refer to the below table) ![alt text][1] **Question** How to turn this table into a pivot table shown in below picture? also how to add to additional columns which is total of each Working and business day status. ![alt text][2] [1]: /storage/temp/3006-count-table.jpg [2]: /storage/temp/3005-pivot.jpg
sql-server-2012pivot
pivot.jpg (56.6 KiB)
count-table.jpg (84.5 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
erlokeshsharma08 avatar image
erlokeshsharma08 answered
You could play arround with the following query. Dont have ddl statements, so trying on the fly with limited data:- SELECT orderid, [OpenWorking] as OpenWorkDays, [OpenCalender] as OpenCalDays, CloseCalender as CloseWorkDays, CloseWorking as CloseCalDays, PendingWorking as PendingWorkDays, PendingCalender as PendingCalDays, Working as Totalwork, Calender as TotalCal FROM (SELECT orderid,daycount,orderstatus+counttype new_col from #tab1 union select orderid,daycount,counttype new_col from #tab1 ) as Source PIVOT ( Sum(DayCount) FOR new_col IN (OpenWorking ,OpenCalender,CloseWorking,CloseCalender,PendingWorking,PendingCalender,Working,Calender ) ) as trg
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.