How do you name columns based on query results?

Asked Jul 14 '14 at 18:05

Active Jul 14 '14 at 18:08

Viewed 54 times

This is really bugging me.

Is there any way to name both the column (i.e.: [John]) and what you are counting (i.e. 'Bob') based on the results of another query? That way I don't have to define each one manually?

For example, let's say I have the following query:

SELECT
   COUNT(CASE WHEN [Name] = 'John' THEN 1 ELSE NULL END) AS [John]
  ,COUNT(CASE WHEN [Name] = 'Bob' THEN 1 ELSE NULL END) AS [Bob]
FROM [database].[dbo].[people]
GROUP BY
   COUNT(CASE WHEN [Name] = 'John' THEN 1 ELSE NULL END) AS [John]
  ,COUNT(CASE WHEN [Name] = 'Bob' THEN 1 ELSE NULL END) AS [Bob]

I tried this with a variable, but not having much luck without some type of loop function to loop through assigning variables and definitions right.

asked Jul 14 '14 at 18:05

Fastidious

1,249
4
25
43

Only with dynamic SQL. Or do the pivoting in your reporting/application layer instead. – Martin Smith Jul 14 '14 at 18:07
2

Column names need to be known when the query is executed, so you'll have to create a list of columns and then execute the sql string using dynamic sql. The duplicate has an example of how you'd do this using pivot. – Taryn Jul 14 '14 at 18:08
Oh man, not dynamic SQL! – Fastidious Jul 14 '14 at 18:10
1

I tried dynamic sql once, it killed my dog – billinkc Jul 14 '14 at 18:11

How do you name columns based on query results?

0 Answers0