Dynamic Column Name

[strom99]

I have the following sub select in my query
(select sum(ord_qty) from t_acct_dmd_detl where (acct_dmd_id = ad.acct_dmd_id and rec_stus_id in (3, 4)) and (datePart(month, dateAdd(day, (-1 * (mfgtran.trnt_days + stocktran.trnt_days)), rqst_del_date))= datePart(month, dateAdd(month, -7, @TODAYSDATE)) and datePart(year, dateAdd(day, (-1 * (mfgtran.trnt_days + stocktran.trnt_days)), rqst_del_date)) = datePart(year, dateAdd(month, -7, @TODAYSDATE)))) as 'S_pre7_orderquantity'

I also use the following for column headers
declare @POST7MONTH as varchar(40)
set @POST7MONTH = subString(dateName(month, dateAdd(month, 7, @TODAYSDATE)), 1, 3) + ' ' + dateName(year, dateAdd(month, 7, @TODAYSDATE))

Can I dynamically name the column 'S_pre7_orderquantity' to display the actual month name of 7 months ago? I have a query that calculates about 44 summary buckets by month. SQLServer 2000.

Thanks Much!!

 

[skuhlman]

you have to build the select as a string and then execute the string:

declare @MonthName varchar(15)
set @MonthName='April'

declare @FinalSQL nvarchar(4000)

set @FinalSQL = '(select sum(ord_qty) from t_acct_dmd_detl where (acct_dmd_id = ad.acct_dmd_id and rec_stus_id in (3, 4)) and (datePart(month, dateAdd(day, (-1 * (mfgtran.trnt_days + stocktran.trnt_days)), rqst_del_date))= datePart(month, dateAdd(month, -7, @TODAYSDATE)) and datePart(year, dateAdd(day, (-1 * (mfgtran.trnt_days + stocktran.trnt_days)), rqst_del_date)) = datePart(year, dateAdd(month, -7, @TODAYSDATE)))) as '+@MonthName

exec (@FinalSQL)