April 8, 2009 at 12:36 pm
Hello, I need some help. What I need is to turn my column output into a row based solution.
My current output is:
Territory fund1 fund2 teamName
----------------------------------------
Central215000.000.00Team1
Northwest0.000.00Team2
Southwest0.000.00Team3
Midwest0.000.00Team4
Southeast0.00235000.00Team5
Northeast0.000.00Team6
My desired output is:
fund Central Northwest Southwest Midwest Southeast Northeast
--------------------------------------------------------------
fund1, 215000, 0.00,0.00,0.00,50000,0.00
fund2, 0.00, 0.00,235000.00,0.00,0.00,0.00
My Sql code is:
(SELECT
r.territory,
sum(CASE WHEN f.description = 'fund1' then t.ticketamt else 0 END) AS [fund1],
sum(CASE WHEN f.description = 'fund2' then t.ticketamt else 0 END) AS [fund2],
r.teamname
FROM tblfunds f, tblTransActions t, tblTerritory r
where ((t.fund = f.description) and
(r.territory = t.territory)) and
(CONVERT(char, t.createDate, 101) = CONVERT(char, GETDATE(), 101)) and t.status <> 'deleted'
group by r.territory, r.teamname
)
union all(
SELECT territory, 0 as [fund1], 0 as [fund2], teamname
FROM tblTerritory
WHERE territory NOT IN
(SELECT
a.territory
FROM tblfunds b, tblTransActions c, tblTerritory a
Where ((c.fund = b.description) and
(c.territory = a.territory)) and
(CONVERT(char, c.createDate, 101) = CONVERT(char, GETDATE(), 101))
)
) order by [fund1]desc
April 9, 2009 at 10:29 am
Have you looked into the PIVOT command?
April 12, 2009 at 8:54 am
See http://www.sqlservercentral.com/articles/T-SQL/63681/ for an excellent alternative to the confusing PIVOT command.
/Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply