问题描述:

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!

网友答案:

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:

  1. 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.
  2. 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]

相关阅读:
Top