title: SQL Query for generating matrix like output querying related table in SQL Server
body: <p>I have three tables:<br>
  <strong>Product</strong>  </p>
  
  <pre><code>ProductID   ProductName  
  1           Cycle  
  2           Scooter  
  3           Car  
  </code></pre>
  
  <p><strong>Customer</strong>  </p>
  
  <pre><code>CustomerID  CustomerName  
  101         Ronald  
  102         Michelle  
  103         Armstrong  
  104         Schmidt  
  105         Peterson   
  </code></pre>
  
  <p><strong>Transactions</strong>   </p>
  
  <pre><code>TID   ProductID CustomerID TranDate   Amount  
  10001 1         101        01-Jan-11  25000.00  
  10002 2         101        02-Jan-11  98547.52  
  10003 1         102        03-Feb-11  15000.00  
  10004 3         102        07-Jan-11  36571.85  
  10005 2         105        09-Feb-11  82658.23  
  10006 2         104        10-Feb-11  54000.25  
  10007 3         103        20-Feb-11  80115.50  
  10008 3         104        22-Feb-11  45000.65  
  </code></pre>
  
  <p>I have written a query to group the transactions like this:  </p>
  
  <pre><code>SELECT P.ProductName AS Product,  
         C.CustName AS Customer,  
         SUM(T.Amount) AS Amount  
  FROM   Transactions AS T  
         INNER JOIN Product AS P  
              ON  T.ProductID = P.ProductID  
         INNER JOIN Customer AS C  
              ON  T.CustomerID = C.CustomerID  
  WHERE T.TranDate BETWEEN '2011-01-01' AND '2011-03-31'   
  GROUP BY  
         P.ProductName,  
         C.CustName  
  ORDER BY  
         P.ProductName  
  </code></pre>
  
  <p>which gives the result like this:  </p>
  
  <pre><code>Product Customer   Amount  
  Car     Armstrong  80115.50  
  Car     Michelle   36571.85  
  Car     Schmidt    45000.65  
  Cycle   Michelle   15000.00  
  Cycle   Ronald     25000.00  
  Scooter Peterson   82658.23  
  Scooter Ronald     98547.52  
  Scooter Schmidt    54000.25  
  </code></pre>
  
  <p><strong>I need result of query in MATRIX form like this:</strong>  </p>
  
  <pre><code>Customer  |------------ Amounts ---------------         
  Name      |Car      Cycle     Scooter  Totals
  Armstrong  80115.50 0.00      0.00     80115.50  
  Michelle   36571.85 15000.00  0.00     51571.85  
  Ronald     0.00     25000.00  98547.52 123547.52  
  Peterson   0.00     0.00      82658.23 82658.23  
  Schmidt    45000.65 0.00      54000.25 99000.90  
  </code></pre>
  
  <p>Please help me to acheive the above result in SQL Server 2005. Using mulitple views or even temporory tables is fine for me.</p>