SELECT invoiceid,
IF(count(*)=1,sum(payment),0) pay1,
IF(count(*)=2,sum(payment),0) pay2,
IF(count(*)=3,sum(payment),0) pay3,
IF(count(*)=4,sum(payment),0) pay4,
IF(count(*)=5,sum(payment),0) pay5,
IF(count(*)=6,sum(payment),0) pay6
FROM payment
GROUP BY invoiceid;
Cheers
/rudy
-----Original Message-----
From: Shazia Fazili [mailto:[EMAIL PROTECTED]
Sent: zaterdag 12 juli 2003 0:19
To: [EMAIL PROTECTED]
Subject: Rows into Columns
Hi,
I have a table PAYMENT which has 3 fields.. PaymentID,InvoiceID,Payment
PaymentID is th eprimary key. For each INvoiceID there can be more than
one payment but less than 6 payments.
PaymentID InvoiceID Payment
1 123 23
2 123 45
3 123 44
4 4567 35
5 4567 67
6 234 64
Now i want a query which will return result as
InvoiceID Pay1 Pay2 Pay3 Pay4 Pay5
123 23 45 44 0 0
4567 35 67 0 0 0
234 64 0 0 0 0
I have older version of Mysql and is less then version 4..so can't use
subquery or union ...
Plz, I need it urgently...
Cheers,
S.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
