title: query won't work after pivot
body: <p>I'm trying to develop a query and i got something like this</p>
  
  <pre><code>SELECT a.No,
         a.Finish,
         a.Shift,
         a.McCode,
         d.NAME,
         b.ItemCode,
         b.ItemName,
         b.Qty,
         b.LotNo,
         b.Description,
         CASE
           WHEN b.Description LIKE '%not good%' THEN 'NG'
           ELSE 'OK'
         END                                     AS OKNG,
         c.ItemCode                              AS matcode,
         c.LotNo                                 AS matlot,
         CASE
           WHEN e.GroupName = 'CONTACT' THEN 'CONTACT'
           ELSE 'Coil/Silver/Wire'
         END                                     AS GroupName,
         ( c.Qty / ( a.Qty + a.QtyNg ) ) * b.Qty AS materialused
  FROM   PPRDDLV a
         LEFT JOIN ICMUTTRAN b
                ON a.PrdNo = b.TranNo
                   AND a.No = b.TranId
         LEFT JOIN ICMUTTRAN c
                ON a.PrdNo = c.TranNo
                   AND a.Finish = c.DatePost
                   AND c.TranTypeID = 6
                   AND c.LotNo &lt;&gt; '0'
         LEFT JOIN popr d
                ON a.OprCode = d.Code
         LEFT JOIN ICITEM e
                ON c.ItemId = e.id
  WHERE  c.qty IS NOT NULL
         AND b.ItemCode IS NOT NULL 
  </code></pre>
  
  <p>I got around 49000 records in around 2 seconds, then I wan't to pivot a column that has 2 kind of value, so I turned it to something like this</p>
  
  <pre><code>SELECT no,
         finish,
         shift,
         mccode,
         NAME,
         itemcode,
         itemname,
         qty,
         lotno,
         description,
         okng,
         matcode,
         matlot
  FROM   (SELECT a.No,
                 a.Finish,
                 a.Shift,
                 a.McCode,
                 d.NAME,
                 b.ItemCode,
                 b.ItemName,
                 b.Qty,
                 b.LotNo,
                 b.Description,
                 CASE
                   WHEN b.Description LIKE '%not good%' THEN 'NG'
                   ELSE 'OK'
                 END                                     AS OKNG,
                 c.ItemCode                              AS matcode,
                 c.LotNo                                 AS matlot,
                 CASE
                   WHEN e.GroupName = 'CONTACT' THEN 'CONTACT'
                   ELSE 'Coil/Silver/Wire'
                 END                                     AS GroupName,
                 ( c.Qty / ( a.Qty + a.QtyNg ) ) * b.Qty AS materialused
          FROM   PPRDDLV a
                 LEFT JOIN ICMUTTRAN b
                        ON a.PrdNo = b.TranNo
                           AND a.No = b.TranId
                 LEFT JOIN ICMUTTRAN c
                        ON a.PrdNo = c.TranNo
                           AND a.Finish = c.DatePost
                           AND c.TranTypeID = 6
                           AND c.LotNo &lt;&gt; '0'
                 LEFT JOIN popr d
                        ON a.OprCode = d.Code
                 LEFT JOIN ICITEM e
                        ON c.ItemId = e.id
          WHERE  c.qty IS NOT NULL
                 AND b.ItemCode IS NOT NULL) AS a
         PIVOT (Max(materialused)
               FOR groupname IN ([CONTACT],
                                 [Coil/Silver/Wire])) AS b 
  </code></pre>
  
  <p>but the query won't complete even after 10 minutes, it won't even show a single records. beside that one, the first query before I used pivot, I put an order by a.no but when I executed it, the result only shows to record 105 but the query is still working and no more record was loaded.</p>
  
  <p>can anyone help me find out what's going on?
  thank you!</p>
  
  <p>Here's the sample data collected from the inner query and the expected value after the query.
  <a href="https://i.stack.imgur.com/SI2ns.png" rel="nofollow noreferrer">enter image description here</a></p>
  
  <p>I realize that I selected "matcode" in the outer select while it shouldn't be there (still didn't work though, and note for the column "material used", I'll be using it later after the pivot is done) </p>