SQL: how do I make this COUNT(CASE WHEN THEN END) AS

Asked Jan 18 '16 at 02:44

Active Jan 18 '16 at 03:21

Viewed 51 times

I have this piece of SQL code that I want to make dynamic:

SELECT
    state as 'hc-a2',
    COUNT(*) AS 'total',
    COUNT(CASE WHEN nature = 'nature_1' THEN nature END) AS 'nature_1',
    COUNT(CASE WHEN nature = 'nature_2' THEN nature END) AS 'nature_2',
    COUNT(CASE WHEN nature = 'nature_3' THEN nature END) AS 'nature_3'
FROM [MY_DATA_BASE].[DBO].[MY_TABLE]
GROUP BY state
ORDER BY state

Because I can't enumerate all the natures in the database I can't keep running a query like this.

I need to make this CASE statement dynamic, this part specifically:

COUNT(CASE WHEN nature = 'nature_1' THEN nature END) AS 'nature_1',
COUNT(CASE WHEN nature = 'nature_2' THEN nature END) AS 'nature_2',
COUNT(CASE WHEN nature = 'nature_3' THEN nature END) AS 'nature_3'

I'm simply stuck because I don't know if it's possible.

Hope someone can enlighten my path on this.

edited Jan 18 '16 at 03:21

chue x

18,573
7
56
70

asked Jan 18 '16 at 02:44

StillBuggin

2

You need dynamic SQL for this. Have you done any research on your own yet? – Tim Biegeleisen Jan 18 '16 at 02:47
1

Google: "sql server dynamic pivot" – Gordon Linoff Jan 18 '16 at 02:48
1

@TimBiegeleisen Yes, I did! That's why I went from a 189 lines query to this with the same result. Now I just need a little help, right?! – StillBuggin Jan 18 '16 at 02:50
1

@GordonLinoff Thank you so much!!! – StillBuggin Jan 18 '16 at 02:50

SQL: how do I make this COUNT(CASE WHEN THEN END) AS

0 Answers0