x

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

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

pivot.jpg (58.0 kB)
count-table.jpg (86.5 kB)
more ▼

asked Nov 05, 2015 at 10:11 AM in Default

avatar image

lokapedia
1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
 
more ▼

answered Nov 05, 2015 at 02:37 PM

avatar image

erlokeshsharma08
2k 3 12 16

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x379
x111

asked: Nov 05, 2015 at 10:11 AM

Seen: 32 times

Last Updated: Nov 05, 2015 at 02:37 PM

Copyright 2017 Redgate Software. Privacy Policy