[RESOLVED] Help with a PIVOT table

[yaplej]

Hi everyone,

I am trying to make a PIVOT table + aggragate data that I cannot quite get working. It is from only two tables.

TicketPayments:
[fkTicketID][intType][decValue]

Tickets:
[pkTicketID][dtDate][intCount][boolPaid]

My goal is to have a query return a table in the following format.
[1][2][3][4][5][Sum(intCount)]

Columes 1-5 are the 5 types found in [intType]. Here is the working PIVOT portion I have. This is getting me a table of the format [1][2][3][4][5].

SELECT 
[1],
[2],
[3],
[4],
[5] 
FROM (SELECT intType, decValue, fkTicketID FROM TicketPayments) o
 PIVOT (SUM(decValue) FOR intType IN([1],[2],[3],[4],[5])) p
 JOIN Tickets c ON p.fkTicketID = c.pkTicketID

I cannot figure how to add the [SUM(intCount)] column to the returned table.

Any points on what I am missing?

[abhijit]

Which database are you using?

[yaplej]

Mssql 2005

[abhijit]

Hi everyone,

I am trying to make a PIVOT table + aggragate data that I cannot quite get working. It is from only two tables.

TicketPayments:
[fkTicketID][intType][decValue]

Tickets:
[pkTicketID][dtDate][intCount][boolPaid]

My goal is to have a query return a table in the following format.
[1][2][3][4][5][Sum(intCount)]

Columes 1-5 are the 5 types found in [intType]. Here is the working PIVOT portion I have. This is getting me a table of the format [1][2][3][4][5].

SELECT 
[1],
[2],
[3],
[4],
[5] 
FROM (SELECT intType, decValue, fkTicketID FROM TicketPayments) o
 PIVOT (SUM(decValue) FOR intType IN([1],[2],[3],[4],[5])) p
 JOIN Tickets c ON p.fkTicketID = c.pkTicketID

I cannot figure how to add the [SUM(intCount)] column to the returned table.

Any points on what I am missing?

Is the join on TICKETID required?

If you use this

SELECT 
[1],
[2],
[3]
FROM (SELECT intType, decValue FROM TicketPayments) o
 PIVOT (SUM(decValue) FOR intType IN([1],[2],[3])) p

you should get your desired results.

[yaplej]

Thanks,

I was finally able to get the data I wanted. I am no SQL expert so figuring this guy out was a pain. There might be a more efficient way of doing this but I dont know it.

Output looks like:
[Date][TotalGuests][TotalCash][TotalCheck][TotalVisa][TotalGift][TotalCoupon]

   
SELECT Date, 
     SUM(TotalGuests) AS TotalGuests, 
     SUM(TotalCash) AS TotalCash, 
     SUM(TotalCheck) AS TotalCheck, 
     SUM(TotalVisa) AS TotalVisa, 
     SUM(TotalGift) AS TotalGift, 
     SUM(TotalCoupon) AS TotalCoupon
FROM (
     SELECT convert(varchar, dtDate, 101) AS Date, 
          SUM(intGuestCount) AS TotalGuests, 
          SUM([1]) AS TotalCash, 
          SUM([2]) AS TotalCheck, 
          SUM([3]) AS TotalVisa, 
          SUM([4]) AS TotalGift, 
          SUM([5]) AS TotalCoupon 
          FROM (SELECT dtDate, 
               intGuestCount, 
               intType, 
               decValue FROM TicketPayments 
          JOIN Tickets ON Tickets.pkTicketID = TicketPayments.fkTicketID ) o
               PIVOT (SUM(decValue) FOR intType IN([1],[2],[3],[4],[5])) p
     GROUP BY dtDate) q
GROUP BY Date