title: SQL Pivot to be used in a View
body: <p>I am trying to create a view that will sum the total hours of different work types, as well as the quantity of different products used to a RefNumber.  </p>
  
  <p>I have successfully pivoted and summed the hours of work grouped by RefNumber, but now I am scratching my head on how to sum the total product use by Ref Number.</p>
  
  <p><a href="https://imgur.com/a/RdlT2bj" rel="nofollow noreferrer">Sample Data</a></p>
  
  <p><a href="https://www.dropbox.com/s/7riyyld3nrh71io/TEstdataXcl.xlsx?dl=0" rel="nofollow noreferrer">Download Excel of data</a></p>
  
  <p>Keep in mind, on some RefNumbers, there will be multiple entries of the same ProductID.  We may have had a few guys using the same product in different quantities.</p>
  
  <p>Here is what I am trying to the Pivot the data too:</p>
  
  <p><a href="https://imgur.com/a/g1nN6wm" rel="nofollow noreferrer">Pivot Data</a></p>
  
  <p>So far, I have been able to accomplish summing the hours of each work type, and pivoting that data into a single line item grouped by the Ref Number using the following SQL code:</p>
  
  <pre><code>SELECT RefNumber,
  SUM (CASE WHEN WorkType = 'Blast' THEN THours ELSE NULL END) AS TBlast,
  SUM (CASE WHEN WorkType = 'Wheel' THEN THours ELSE NULL End) As TWheel,
  SUM (CASE WHEN WorkType = 'Painting' THEN THours ELSE NULL END) AS TPainter,
  SUM (CASE WHEN WorkType = 'Mask/Prep' THEN Thours ELSE NULL END) As TMask,
  SUM (CASE WHEN WorkType = 'Demask/Touch Up' THEN Thours ELSE NULL END) As TDMask,
  SUM (CASE WHEN WorkType = 'Handling: Raw' THEN THours ELSE NULL END) As TRHand,
  SUM (CASE WHEN WorkType = 'Handling: Product' THEN Thours ELSE NULL END) As TPHand,
  SUM (CASE WHEN WorkType = 'Wheel:Assist' THEN Thours ELSE NULL END) As TAWheel,
  SUM (CASE WHEN WorkType = 'Metalizing' THEN Thours ELSE NULL END) As TMetal
  
  
  FROM (
  SELECT [RefNumber], [WorkType], SUM (Hours)/60 As THours
    FROM [dbo].[Vw_Beta_CostLog]
    GROUP BY RefNumber, WorkType
  
  ) sub
  GROUP BY RefNumber
  ORDER BY RefNumber
  </code></pre>
  
  <p>Any Ideas on how to modify this code base to Pivot the the distinct product ID's into there own column, and sum the use of those products in a second column?</p>
  
  <p>Also, I want to be able to use this as a view, so I am trying to avoid dynamic pivots.</p>
  
  <p>EDIT:  Forgot to mention, there will be at most 4 unique products used per ref number. </p>
  
  <p>RAW DATA</p>
  
  <pre><code>GUID        EmpName         RefNumber       DateInt     Hours   WorkType         ProductID  PQty
  P-3468      Gary Hahn       114204      20181008            132 Painting                NULL    NULL
  P-3473      Gary Hahn       114204      20181009            204 Painting                NULL    NULL
  P-3475      Gary Hahn       114204      20181009            120 Painting                NULL    NULL
  F-31915     Jose Flores     114204      20181007            60  Handling: Raw           NULL    NULL
  F-31941     Jose Flores     114204      20181008            30  Handling: Raw           NULL    NULL
  F-31951     Chris Pollock   114204      20181008            30  Handling: Raw           NULL    NULL
  F-32076     Chris Pollock   114204      20181010            120 Handling: Product       NULL    NULL
  F-32109     Chris Pollock   114204      20181011            90  Handling: Product       NULL    NULL
  F-32301     Daryl Underwood 114204      20181015            15  Handling: Product       NULL    NULL
  B-6594      David Martinez  114204      20181007            150 Blast                   NULL    NULL
  B-6599      Emiliano Barrios 114204     20181008            66  Blast                   NULL    NULL
  B-6617      Jose Molina     114204      20181009            30  Blast                   NULL    NULL
  P-3468      Gary Hahn       114204      20181008        NULL    Primer                      11     3
  P-3473      Gary Hahn       114204      20181009        NULL    Intermediate            890    2
  P-3475      Gary Hahn       114204      20181009        NULL    Finish                  134HG  2
  </code></pre>
  
  <p>Output I am looking for</p>
  
  <pre><code>RefNumber   Blast   Painting    Handling: Raw   Handling: Product   Product1    P1Qty   Product2    P2Qty   Product3    P3Qty   
  114204      246     456           120            225                    11          3          890  2         134HG            2    
  </code></pre>