[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?

Which database are you using?

Mssql 2005

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.

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

---