[RESOLVED] Help with a PIVOT table
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].
Code:
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?
Re: Help with a PIVOT table
Which database are you using?
Re: Help with a PIVOT table
Re: Help with a PIVOT table
Quote:
Originally Posted by
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].
Code:
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
Code:
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.
Re: Help with a PIVOT table
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]
Code:
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