SQL Pivots

Reference: https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/

The First Step is to get the Table in the format you need - so you can pivot it.

SELECT     
CollectionID = CC.CollectionID, 
TransferCallType = TransferCallType,
SUM(ISNULL(CTRT.RouterCallsQNow, 0)) AS CallsQueuedNow
 
FROM        
dbo.Call_Type_Real_Time(nolock) AS CTRT 
INNER JOIN dbo.v_Collection_Calltypes AS CC WITH (nolock) ON CTRT.CallTypeID = CC.CallTypeID
 
GROUP BY 
CC.CollectionID, CC.CollectionName, CC.TransferCallType

What we want

• a single row for each CollectionID
• have a column for TransferType = “N” and the data in this column will be the CallsQueuedNow value
• have a column for TransferType = “Y” and the data in this column will be the CallsQueuedNow value

In this example TransferType can only every be 'Y' or 'N'.

Since the Header value for these two columns would end up as “Y” or “N” - which is not very helpful - lets change the value of the TransferCallType as per below:

SELECT     
CollectionID = CC.CollectionID, 
TransferCallType = CASE WHEN CC.TransferCallType = 'N' THEN 'CIQ_IVR' ELSE 'CIQ_Transfer' END,
SUM(ISNULL(CTRT.RouterCallsQNow, 0)) AS CallsQueuedNow
 
FROM        
dbo.Call_Type_Real_Time(nolock) AS CTRT 
INNER JOIN dbo.v_Collection_Calltypes AS CC WITH (nolock) ON CTRT.CallTypeID = CC.CallTypeID
 
GROUP BY 
CC.CollectionID, CC.CollectionName, CC.TransferCallType

SELECT 
* 
FROM(
SELECT     
CollectionID = CC.CollectionID, 
TransferCallType = CASE WHEN CC.TransferCallType = 'N' THEN 'CIQ_IVR' ELSE 'CIQ_Transfer' END,
SUM(ISNULL(CTRT.RouterCallsQNow, 0)) AS CallsQueuedNow
 
FROM        
dbo.Call_Type_Real_Time(nolock) AS CTRT 
INNER JOIN dbo.v_Collection_Calltypes AS CC WITH (nolock) ON CTRT.CallTypeID = CC.CallTypeID
 
GROUP BY 
CC.CollectionID, CC.CollectionName, CC.TransferCallType
) t
 
Pivot( MAX(CallsQueuedNow) FOR TransferCallType IN ([CIQ_IVR],[CIQ_Transfer]) ) AS temp_table

You can see above is a select * from our table Pivoted with the following line:

Pivot( MAX(CallsQueuedNow) FOR TransferCallType IN ([CIQ_IVR],[CIQ_Transfer]) ) 

i.e. we are Pivoting around the column with the data we want to show. In this case the CallsQueuedNow . With a Pivot like you do with Group, you need to do SUM or MAX on the Value. A Max will not change our value. We then need to decide which CallSQueuedNow values appear under what Columns and in our case this we want the columns

• CIQ_IVR
• CIQ_Transfer

We want to improve the output of the above SQL somewhat - as we don't want to show NULL and would prefer to show this up as '0'. We also add an additional column where we can sum two of our column values together.

This is easy to fix - so instead of using a SELECT *, define the output of the SQL Table as per below.

SELECT 
CollectionId,
CIQ_IVR = isNull(CIQ_IVR,0),
CIQ_Transfer = isNull(CIQ_Transfer,0),
CIQ_Total =  isNull(CIQ_IVR,0) + isNull(CIQ_Transfer,0)
 
FROM(
SELECT     
CollectionID = CC.CollectionID, 
TransferCallType = CASE WHEN CC.TransferCallType = 'N' THEN 'CIQ_IVR' ELSE 'CIQ_Transfer' END,
SUM(ISNULL(CTRT.RouterCallsQNow, 0)) AS CallsQueuedNow
 
FROM        
dbo.Call_Type_Real_Time(nolock) AS CTRT 
INNER JOIN dbo.v_Collection_Calltypes AS CC WITH (nolock) ON CTRT.CallTypeID = CC.CallTypeID
 
GROUP BY 
CC.CollectionID, CC.CollectionName, CC.TransferCallType
) t
 
Pivot( MAX(CallsQueuedNow) FOR TransferCallType IN ([CIQ_IVR],[CIQ_Transfer]) ) AS temp_table