Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
tech-notes:sql:pivot [2022/09/22 13:08] – [Our Original Table] gerardorourketech-notes:sql:pivot [2022/09/22 13:21] (current) – [Pivot of our Source Table] gerardorourke
Line 29: Line 29:
  
   *a single row for each **CollectionID**    *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** = "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+  *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'. In this example TransferType can only every be 'Y' or 'N'.
Line 36: Line 36:
 ==== Our Source Table ==== ==== Our Source Table ====
 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: 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:
 +Note: this step is not particularly necessary - we could just change the column names at the end - in our final SQL table.
  
 <code sql> <code sql>
Line 79: Line 80:
 </code> </code>
  
-You can see above is a select * from our table Pivoted with the following line:+You can see above is a SELECT * of our **source** table Pivoted with below following line:
 <code sql>Pivot( Max(CallsQueuedNow) FOR TransferCallType IN ([CIQ_IVR],[CIQ_Transfer]) ) </code> <code sql>Pivot( Max(CallsQueuedNow) FOR TransferCallType IN ([CIQ_IVR],[CIQ_Transfer]) ) </code>
  
Line 88: Line 89:
 i.e. we are Pivoting around the column with the data we want to show. In this case the CallsQueuedNow . 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. 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+ 
 +We then need to columns we want the CallSQueuedNow values to appear under and in our case this we want the columns
   *CIQ_IVR   *CIQ_IVR
   *CIQ_Transfer   *CIQ_Transfer
  • tech-notes/sql/pivot.1663848524.txt.gz
  • Last modified: 2022/09/22 13:08
  • by gerardorourke