question

benisanders avatar image
benisanders 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
columncase-statementaliascase
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

benisanders avatar image benisanders commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.