t-sql pivot filter based on input column name

Question

Based on following AreaState table

Area    State
-------------------
A1  Active
A1  Active
A1  Active
A1  Proposed
A1  Proposed
A2  Active
A2  Proposed

I want to write a stored proc that returns count of state for each of the areas. Input to the stored proc is any valid State (in this case @state is the input parameter). I was hoping that below would work but it does not.

declare @state varchar(10)
set @state = 'Active'

select Area, QUOTENAME(@state)
from
(
  select Area, State from AreaState
) as src
pivot
(
   count(State) for State in (QUOTENAME(@state))
) as pvt

Pls. suggest.

Answer 1

I may have the requirements wrong while I'm waiting for feedback but I think you can use a simple GROUP BY query.

Code:

SELECT Area, State, Count(*) as 'Count'
FROM x
GROUP BY Area, State

Test case:

WITH x AS (
    SELECT 'A1' as Area, 'Active' as State
    UNION ALL
    SELECT 'A1' as Area, 'Active' as State
    UNION ALL
    SELECT 'A1' as Area, 'Active' as State
    UNION ALL
    SELECT 'A1' as Area, 'Proposed' as State
    UNION ALL
    SELECT 'A1' as Area, 'Proposed' as State
    UNION ALL
    SELECT 'A2' as Area, 'Active' as State
    UNION ALL
    SELECT 'A2' as Area, 'Proposed' as State
)
SELECT Area, State, Count(*) as 'Count'
FROM x
GROUP BY Area, State

Output:

Area    State      Count
------------------------
A1      Active     3
A1      Proposed   2
A2      Active     1
A2      Proposed   1

Answer 2

I am not sure if this is the best approach but it does solve the issue.

declare @state varchar(20)
set @state = 'Proposed'

create table #tt_Result
(
  Area varchar(max),
  [Active] int,
  [Proposed] int
)

insert into #tt_Result
select Area, [Active], [Proposed]
from
(
  select Area, State from AreaState
) as src
pivot
(
   count(State) for State in ([Active], [Proposed])
) as pvt

select Area, 
case
  when @state = 'Active' then [Active]
  when @state = 'Proposed' then [Proposed]
end as [Count]
from #tt_Result

drop table #tt_Result