title: Assigning a value of data for each record having the same condition in SQL Server 2008
body: <p>I have a table in SQL Server 2008 like:</p>
  
  <pre><code>Period    Name     Value
  1         A        10
  2         A        20
  3         A        30
  4         A        40
  1         B        50          
  2         B        80
  3         B        70
  4         B        60
  </code></pre>
  
  <p>What I need to write a select query includes a new column <code>MainValue</code> which contains the value where period=4 for a name for each data. </p>
  
  <p>Example:</p>
  
  <pre><code> Period     Name     Value    MainValue
      1         A        10       40
      2         A        20       40
      3         A        30       40
      4         A        40       40
      1         B        50       60 
      2         B        80       60
      3         B        70       60
      4         B        60       60
  </code></pre>
  
  <p>How can I provide this? I tried the one below, but it is not working as I want.</p>
  
  <pre><code>Select 
      *, 
      (select Value where Period = 4) as MainValue 
  from myTable;
  </code></pre>
  
  <p>Any help would be appreciated.</p>