columns to rows for 3 tables

  • 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

  • Have you looked into the PIVOT command?

  • See http://www.sqlservercentral.com/articles/T-SQL/63681/ for an excellent alternative to the confusing PIVOT command.

    /Paul

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply