asked
Can I have Dynamic Column Name based on Nested Case When
Hi I am in SQL Server 2008 R2 and am working with some temp tables i created. i am hoping to be able to have a dynamic name for a column based on which instance of a nested Case When is used. my code is below works as long as the alias's are commented out. Basically i am looking to have the last column name be PageQTY if tt.metric = 1 and MBQTY when tt.metric = 2. I have also tried to use "as" to name the sub cases but that doesnt work either. I dont know if this is actually possible, but appreciate any input. Cheers -Ben Select tt3.*,(case when tt.metric = 1 then 'PageQTY'= (case when tt3.chargetype = 'Volume Change' then convert(decimal(18,2),tt2.PageIncremental) when tt3.chargetype = 'Created' then tt.Included else MAX(tt.pageHWM) end) when tt.metric = 2 then 'MBQTY'= (case when tt3.chargetype = 'Volume Change' then convert(decimal(18,2),tt2.MBIncremental) when tt3.ChargeType = 'Created' then tt.included else MAX(tt.MBHWM) end) else 'hi' end) from #temp3 tt3 left join #temp2 tt2 on tt2.contract_id = tt3.contractid and tt2.BillMo = tt3.billmo left join #temp tt on tt.contract_id = tt3.ContractID and tt.usagestart <= tt3.billmo group by tt3.contractid,tt3.BillMo,tt3.ChargeType,tt3.ChargeAmt, tt2.PageIncremental,tt2.MBIncremental,tt.Included,tt.metric order by BillMo desc
No. CASE is an expression, in other words it handles data. It is not a logical processing construct in the way you are trying to use it. If you really want the result set to have different column names then you will need need 2 logical paths through the code, using IF...ELSE...END. Or have 2 separate columns in the one result set. But how does your application handle the fact that the column names might change?
Hi, thanks for the quick reply. I figured as much :-( The result of this is for presentation only and is not used anywhere else, since I only need the one result set or the other I'd rather not separate them.
15 People are following this question.
