SQL Server Performance Forum – Threads Archive
Combining 2 rows into 1 with select
The data is stored as follows; One record per booking and the record containsagent X16,
amt Money,
month_year x6,
branch X2 I need to find a way to select the top 20 agents with total sales for a given branch and departure range and also show the total sales for that agent against a second date range. The data needs to be returned in one record. Something like Branch = ABC
First Year/Month Range Aug 2006 to Oct 2006
Second Year/Month Range Mar 2005 to Apr 2005 Branch Agent Sales Month Year Past Sales Past Date ABC John 20000 Sep 2006 1000 Mar 2005
ABC Leo 19700 Oct 2006 111 Apr 2005
ABC Mike 6000 Aug 2006 0 Mar 2005 Perhaps I could create a view, not sure. The reason I need it to be returned in one row is that the 3rd party software I am using can only create charts from single select statements based on tables or views Any help would be appreciated
something like this
select branch, agent,
[2006 Sep] = sum(case when month_year = ‘200609’ then amt else 0 end),
[2006 Oct] = sum(case when month_year = ‘200610’ then amt else 0 end)
from table
group by branch, agent KH
]]>