How to create a computed column name using another column's value (sql server 2008)

Question

I need to use the value of a column in the name of a new column....this is the line I need help with:

Count([DepartmentName]) As [[DepartmentName] + "Emails"]

Code:

SELECT 
   [CustomerId], 
   @MetricMonth AS "MetricMonth", 
   @MetricYear AS "MetricYear",
   [LeadType], [DeviceTypeId], [DepartmentName], 
   Count([DepartmentName]) As [[DepartmentName] + "Emails"]
FROM 
   [myTable]
WHERE 
   LeadType = @LeadType
   AND _CreateDate BETWEEN @StartDateTime AND @EndDateTime
GROUP BY 
   [CustomerId], [LeadType], [DeviceTypeId], [DepartmentName]

The reason for the need is that the receiving table has columns labeled as such and this seems like the cleanest way to do it. There are 16 possible values for DepartmentName so I don't want to have a bunch of case statements.

Here's a sample of the result. There will be multiple groups because of DepartmentName and DeviceTypeId.

 CustomerId MetricMonth MetricYear  LeadType    DeviceTypeId    DepartmentName     NewName
 28590  4           2014            Email           1           New          9
 36980  4           2014            Email           1           Finance          3
 876            4           2014            Email           1           New         9

Thanks!

What if you have more than one group in your result? It is impossible that each row have its one column name. — Hamlet Hakobyan, Jun 02 '14 at 18:33
Sounds like you need a PIVOT query. Otherwise I don't know how you can have two different department names in the results unless they each have their own count column. — Aaron Bertrand, Jun 02 '14 at 18:34
@HamletHakobyan Yes, I expect more than 1 group. I edited the question details...Thanks! This will serve as a daily aggregation of each of the lead types per device types and departments. — Will Lopez, Jun 02 '14 at 18:45
@AaronBertrand Thanks for the suggestion. I'll look into the PIVOT query. Thanks! — Will Lopez, Jun 02 '14 at 18:46

score 1 · Accepted Answer · edited May 23 '17 at 11:51

You in effect want a column name that has multiple values, ie a column with multiple names, which is just impossible in any flavor of SQL, afaik.

Short of that, you have two options:

if you really want columns with names like "Department1 Emails" then you will have to pivot the data (and you'll have to hard-code all the Department Names). If that is what you want see here.
if you just want a column called "Department Emails" with values such as "Department1 Emails: 30" then you can do this:

SELECT [DepartmentName], [DepartmentName] + ' Emails: ' + CAST(COUNT([DepartmentName]) AS VARCHAR(20))

FROM [myTable]

GROUP BY [DepartmentName]

pivot is cool, thanks. I've heard about it before but didn't really know it's application hence I shied away from it :-). — Will Lopez, Jun 02 '14 at 19:48

How to create a computed column name using another column's value (sql server 2008)

1 Answers1