title: Fine tuning MS SQL server query joining two tables with grouped result
body: <p>I would like to ask you if there's efficient way to write a query to retrieve the data from tables provided below and display only the columns and values I want. I already wrote two queries however the first one is a bit slow and in the second I got more columns than I want. </p>
  
  <p>So here are my three tables:</p>
  
  <p><a href="https://i.stack.imgur.com/gU4lb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gU4lb.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/E7noW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/E7noW.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/OQkV7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/OQkV7.png" alt="enter image description here"></a></p>
  
  <p>And here's the output I want:
  <a href="https://i.stack.imgur.com/dIEB5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dIEB5.png" alt="enter image description here"></a></p>
  
  <p>Output table should display monthly values from Jan till Dec.
  In the real database Table A has ~40k rows, Table B has 20 rows and Table C has ~1 million rows.
  Below you can see part of a query which I wrote and which generate desired output, however when I ran it for real DB it takes ~20-30 seconds:</p>
  
  <pre><code>  SELECT TableA.Id
      ,TableA.Title
      ,TableA.Description
      ,TableB.Title
      ,f1.[Value] as TableC_JanuaryValue
      ,f2.[Value] as TableC_FebruaryValue
      ....
      FROM &lt;tablename&gt;
  INNER JOIN TableB ON TableB.Id = TableA.TableB_FK_Id
  INNER JOIN TableC as f1 ON (f1.TableA_FK_Id = TableA.Id AND f1.[Year] = 2018 AND f1.[Month] = 1)
  INNER JOIN TableC as f2 ON (f2.TableA_FK_Id = TableA.Id AND f2.[Year] = 2018 AND f2.[Month] = 2)
  .....
  </code></pre>
  
  <p>I was trying to write it differently and make it faster and I managed to get it down to 2-3 seconds, however the output is not exactly what I want. 
  Here's the second query:</p>
  
  <pre><code>  SELECT TableA.Id
      ,TableA.Title
      ,TableA.Description
      ,TableB.Title
      ,count(case when TableC.Value = 'V1' and TableC.[Month] = 1 then TableC.Id end) as JAN_IsV1
      ,count(case when TableC.Value = 'V2' and TableC.[Month] = 1 then TableC.Id end) as JAN_IsV2
      ,count(case when TableC.Value = 'V3' and TableC.[Month] = 1 then TableC.Id end) as JAN_IsV3
      ,count(case when TableC.Value = 'V1' and TableC.[Month] = 2 then TableC.Id end) as FEB_IsV1
      ,count(case when TableC.Value = 'V2' and TableC.[Month] = 2 then TableC.Id end) as FEB_IsV2
      ,count(case when TableC.Value = 'V3' and TableC.[Month] = 2 then TableC.Id end) as FEB_IsV3
      ....
      FROM &lt;tablename&gt;
  INNER JOIN TableB ON TableB.Id = TableA.TableB_FK_Id
  INNER JOIN TableC ON TableA.Id = TableC.TAbleA_FK_Id
  where TableC.[Year] = 2018
  group by TableA.Id, TableA.Title, TableA.Description, TableB.Title
  </code></pre>
  
  <p>And here's what I get:
  <a href="https://i.stack.imgur.com/lLSjW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lLSjW.png" alt="enter image description here"></a></p>
  
  <p>I know that its very close to the result I want, however its not exactly the same :( </p>