Select with dynamic columns occurrences

Question

With a 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 a 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 by name with dynamic columns, group by country and state)

Is it possible to build it with just one sentence?

Answer 1

You need pivot operation to achieve the result.

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

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

Answer 2

If you want a dynamic way:

SAMPLE DATA

create table MyTable(
    name varchar(20),
    country varchar(20),
    state varchar(20)
)
insert into MyTable values
('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');

SOLUTION USING CROSSTAB

declare @sql1 as varchar(4000) = ''
declare @sql2 as varchar(4000) = ''
declare @sql3 as varchar(4000) = ''

select @sql1 = 
'select
    country
    ,state
'

select @sql2 = @sql2 +
'   ,sum(case when name = ''' + name + ''' then 1 else 0 end) as [' + name + ']' + char(10)
from (
    select distinct name from MyTable
)t
order by name

select @sql3 = 
'from MyTable
group by country, state
order by country, state desc
'

print (@sql1 + @sql2 + @sql3)
exec (@sql1 + @sql2 + @sql3)