t-sql pivot filter based on input column name
- by stackoverflowuser
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.
