title: SQL Server 2008 R2: Pivot table query performance
body: <p><strong>Table</strong>: Product</p>
  
  <pre><code>create table Product
  (
      productID int,
      productName varchar(20),
      productsalesdate DATETIME,
      producttype varchar(20)
  );
  </code></pre>
  
  <p><strong>Insertion</strong>:</p>
  
  <pre><code>insert into product values(1,'PenDrive','2010-01-01','Electronic');
  insert into product values(1,'Computer','2016-01-01','Electronic');
  insert into product values(1,'Laptop','2011-02-02','Electronic');
  insert into product values(2,'textbook','2014-02-02','books');
  insert into product values(2,'notebook','2016-01-01','books');
  insert into product values(3,'Car','2016-01-01','Vehicle');
  insert into product values(3,'Bike','2016-01-07','Vehicle');
  </code></pre>
  
  <p><strong>First Try</strong>: In this I am getting wrong sum of productType </p>
  
  <pre><code>SELECT productID, FirstSale,LastSale, [Electronic],[books],[Vehicle]
  FROM
  (
      SELECT 
          productID, 
          MIN(ProductSalesdate) as FirstSale,
          MAX(ProductSalesdate) as LastSale,
          productType
      FROM
          Product
      Group by productID,productType
  ) a 
  PIVOT
  (
      COUNT(productType) 
      FOR productType IN ( [Electronic],[books],[Vehicle] )
  ) AS pvt;   
  </code></pre>
  
  <p><strong>Second Try</strong>: In this try I have solved the sum problem but the query is taking more time for execute for huge records. </p>
  
  <pre><code>SELECT productID,FirstSale,LastSale ,[Electronic],[books],[Vehicle]
  FROM
  (
      SELECT a.ProductID, a.FirstSale, a.LastSale, b.ProductType
      FROM Product b
      inner join
      (
          SELECT 
              productID, 
              MIN(ProductSalesdate) as FirstSale,
              MAX(ProductSalesdate) as LastSale
          FROM
              Product
          Group by productID
      ) as a 
      ON a.ProductID = b.ProductID
  ) ab 
  PIVOT
  (
      COUNT(productType) 
      FOR productType IN ( [Electronic],[books],[Vehicle] )
  ) AS pvt;   
  </code></pre>
  
  <p><strong>Note</strong>: The second query is works fine but the problem is with the performance, because of 
  I am joining two same table because to get count of productType in the pivot query.</p>
  
  <p><strong>Question</strong>: How to optimize the second query which is a my second try?</p>