title: SQL JOIN on Dynamic Column based on Variable
body: <p>I have an image summary table [summary] that will serve as a reporting table in the near future. There is a reference table [views] and a third table that the image team populates [TeamImage]. The summary table has 1 row per part number (table has distinct part numbers) and many columns of image views (TOP, BOT, FRO, BAC, etc.). The [views] table lists each of these views with an id field, which is an IDENTITY field. The [TeamImage] table contains part numbers and views (part number field is not unique as the part numbers will be listed multiple times as they have image views).</p>
  
  <p>Example:</p>
  
  <pre><code>    TABLE [summary]
  Part_Number | TOP | BOT | FRO | BAC |
  12345       |     |     |     |     |
  67890       |     |     |     |     |
  
      TABLE [views]
  id   | View |
  1    | TOP  |
  2    | BOT  |
  3    | FRO  |
  4    | BAC  |
  
      TABLE [TeamImage]
  PartNum     | View |
  12345       | TOP  |
  12345       | BOT  |
  12345       | FRO  |
  12345       | BAC  |
  67890       | FRO  |
  67890       | BAC  |
  </code></pre>
  
  <p>Here's what I need in the end:</p>
  
  <pre><code>    TABLE [summary]
  Part_Number | TOP | BOT | FRO | BAC |
  12345       |  1  |  1  |  1  |  1  |
  67890       |     |     |  1  |  1  |
  </code></pre>
  
  <p>I could run several update queries but I have 27 views and about 2 million part numbers. I was hoping I could run something like below, even though I know I cannot use a variable as the column name:</p>
  
  <pre><code>DECLARE @id int = (SELECT max(id) FROM [views]), @ViewType nvarchar(3);
  
  WHILE @id IS NOT NULL
  BEGIN
  
      SELECT @ViewType = (SELECT [View] FROM [views] WHERE id = @id);
  
      UPDATE a
      SET a.[@ViewType] = '1'
      FROM [summary] a
      INNER JOIN [TeamImage] b
      AND a.[Part_Number] = b.[PartNum]
  
      WHERE b.[View] = @ViewType;
  
  SELECT @id = max(id) FROM [views] WHERE id &lt; @id;
  
  END;
  </code></pre>
  
  <p>Basically, I was hoping to use a variable to grab the different views from the [views] table (id = 27 down to id=1...could have counted up but doesn't matter) and populate the corresponding field in the [summary] table.</p>
  
  <p>I know the SET a.[@ViewType] = '1' won't work, and a colleague of mine mentioned using VB but didn't know if that really was the most efficient option. I understand that I could use a PIVOT on the [TeamImage] table, but I'm not sure that will allow me to update my [summary] table (which has many more fields in it than just the image views). It still seems I need something that will effectively loop through update queries. I could write 4 update queries, one for each view (although my real table has 27 views), but I need something more dynamic in case we add views in the future.</p>