sql server - Select with dynamic columns occurrences -

with table like:

name    country   state -----   -------   ----- ana     usa        ca paul    usa        wy louis   can        ot ana     usa        ca john    can        al paul    usa        wy john    usa        ca ana     usa        ca louis   can        ot 

i need result like:

country    state   ana   paul  louis  john   --------   -----   ----  ----  -----  ----   usa        ca       3     1     0      1            wy       0     1     0      0 can        ot       0     0     2      0            al       0     0     0      1 

(count occurrences name dynamic columns, group country , state)

is possible build 1 sentence?

you need pivot operation achieve result.

http://sqlfiddle.com/#!3/aae09/3

select country, state,         sum(case when name='ana' 1 else 0 end) ana,        sum(case when name='paul' 1 else 0 end) paul,        sum(case when name='john' 1 else 0 end) john,        sum(case when name='louis' 1 else 0 end) louis table_name group country, state order country,state desc;