Select statement inside a SUM function

Question

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.

all of the dynamic columns exists in a other table(TableB).

Now i need to make a query like that

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

I'm working with SQL 2000. Thanks in advance.

Example:

*

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)

Answer 1

There is no need to incude the TABLE B here, You can get your desired result by the first table only.

     SELECT 
          Orgno,
          SUM(Week1) Week1,
          SUM(Week3) Week3, 
          SUM(Week4) Week4, 
          SUM(Week7) Week7
    FROM 
          TableA 
    GROUp BY 
          Orgno

UPDATE

From your comment what I understand is that you dont know the column name for first table and you want to get that column name from the second table in that case you need to use dynamic SQL. Instead of firing the multiple queries to gt each column you can get that in single query and concat with dynamic sql string. You can try something like this,

declare @sql  varchar(max)
set @sql = 'SELECT Orgno,' 

declare @tbl varchar(100)
set @tbl = 'tableB' -- put your table name here

SELECT @sql = @sql +'SUM(' + name + '),'
FROM tablB
WHERE Id IN(1,2,3,4) 

set @sql = LEFT(@sql, LEN(@sql) - 1)
set @sql = @sql + ' FROM ' + @tbl + ' Group BY Orgno'

EXEC (@sql)

More info on dynamic sql.

Answer 2

You need to construct string containing SQL query and then run it with EXEC. If you need it to be a single statement wrap it to stored procedure.

Look at examples here

Your case will probably look like this

DECLARE @sqlCommand varchar(1000)
DECLARE @column1 varchar(128)
DECLARE @column2 varchar(128)
SELECT @column1 = ColName FROM TableB WHERE id=1
SELECT @column2 = ColName FROM TableB WHERE id=2
SET @sqlCommand = 'SELECT SUM(' + @column1 + '), SUM(' + @column2 +') FROM TableAO GROUP BY OrgNo'
EXEC (@sqlCommand)