>>Hi all -
>>
>>I have this SQL which seems to work OK. Suggestions are welcome.
>>

>>		SQL = "select f.FundType, "
>>		SQL += "(select sum(amount) from TIP_ApprovedFunding where FundType=f.FundType and YEAR=@TipYear) as Year1, "
>>		SQL += "(select sum(amount) from TIP_ApprovedFunding where FundType=f.FundType and YEAR=@TipYear + 1) as Year2, "
>>		SQL += "(select sum(amount) from TIP_ApprovedFunding where FundType=f.FundType and YEAR=@TipYear + 2) as Year3, "
>>		SQL += "(select sum(amount) from TIP_ApprovedFunding where FundType=f.FundType and YEAR=@TipYear + 3) as Year4, "
>>		SQL += "(select sum(amount) from TIP_ApprovedFunding where FundType=f.FundType and YEAR=@TipYear + 4) as Year5, "
>>		SQL += "SUM(amount) as Total "
>>		SQL += "From TIP_ApprovedFunding f "
>>		SQL += "inner join TIP_Projects p on f.TipID = p.TipID "
>>		SQL += "where p.ApprovedStatus = 'Active' and f.YEAR between @TipYear and @TipYear+4 "
>>		SQL += "group by f.FundType"
>>

>>I would like to replace the column names Year1, Year2, etc with the actual year as established by the @TipYear parameter, but I've not been able to find a way to do this. Can someone help?
>>Thanks
>
>Try
>
>

>SQL = @"select f.FundType, 
>		sum(CASE WHEN  f.YEAR=@TipYear THEN f.amount END) AS "  + ParameterYear + 
>		@",sum(CASE WHEN  f.YEAR=@TipYear+1 THEN f.amount END)  AS " + ParameterYear+1 + 
>                etc. +
>		@",SUM(amount) as Total 
>		From TIP_ApprovedFunding f 
>		inner join TIP_Projects p on f.TipID = p.TipID 
>		where p.ApprovedStatus = 'Active' and f.YEAR between @TipYear and @TipYear+4 "
>		group by f.FundType";
>

>The above is assuming you were talking about C# code and want to pass @TipYear as parameter in your code.
>
>BTW, since you don't need to select columns from Projects table I suggest to change the join into WHERE EXISTS condition, e.g.
>WHERE exists (select 1 from TIP_Projects p where f.TipID = p.TipID and p.ApprovedStatus = 'Active')
>
>---------------------------
>Also, you can see I changed the query from what you used to a simpler version. In my version I assume you only want to get amounts for active projects.

Thanks Naomi. In addition to answering my question, your suggestion solves a problem I didn't know I had.