title: Select statement inside a SUM function
body: <p>I have two table. 1st table(TableA) has some column like week1, week3, week4, Week7 (dynamic) along with some static column like, orgno, employeeID, employee name.</p>
  
  <p>all of the dynamic columns exists in a other table(TableB).</p>
  
  <p>Now i need to make a query like that</p>
  
  <pre><code>SELECT OrgNo, 
  SUM(SELECT ColName FROM TableB WHERE id=1), 
  SUM(SELECT ColName FROM TableB WHERE id=2),
  SUM(SELECT ColName FROM TableB WHERE id=3),
  SUM(SELECT ColName FROM TableB WHERE id=4),
  FROM TableAO
  GROUP BY OrgNo
  </code></pre>
  
  <p>I'm working with SQL 2000.
  Thanks in advance.</p>
  
  <p>Example:</p>
  
  <p>*</p>
  
  <pre><code>TableA:*
  **Orgno   EmployeeID   EmployeeName   Week1   week3   week4   Week7** 
  (1         1            XX             0       5       4       3)
  (1         2            YY             1       6       0       2)
  (2         1            XX             0       5       4       3)
  (1         3            ZZ             1       6       0       2)
  
  *TableB:*
  **ID   Name**
  (1    Week1)   
  (2    Week3)   
  (3    Week4)   
  (4    Week7)
  
  *Desire Output:* 
  **Orgno   Week1   week3   week4   Week7**    
  (1        2       17       4       7)
  (2        0        5       4       3)
  </code></pre>