RE: Rows into Columns

[Rudy Metzger]

In the following statement replace STRING by 

concat(group_concat(payment ORDER BY paymentid SEPARATOR '|'),'|')


SELECT 
  invoiceid,
  if ( STRING IS NOT NULL,
       substring(STRING, 1,
                 if( locate('|',STRING) > 0, 
                     locate('|',STRING)-1,
                     length(STRING)
                    )
                 ),
       NULL) pay1,
  if ( STRING IS NOT NULL,
       if ( substring_index(STRING,'|',2) IS NOT NULL,
            substring_index(substring_index(STRING,'|',2),-1),
            NULL
           ),
       NULL) pay2,
  if ( STRING IS NOT NULL,
       if ( substring_index(STRING,'|',3) IS NOT NULL,
            substring_index(substring_index(STRING,'|',3),-1),
            NULL
           ),
       NULL) pay3,
  if ( STRING IS NOT NULL,
       if ( substring_index(STRING,'|',4) IS NOT NULL,
            substring_index(substring_index(STRING,'|',4),-1),
            NULL
           ),
       NULL) pay4,
  if ( STRING IS NOT NULL,
       if ( substring_index(STRING,'|',5) IS NOT NULL,
            substring_index(substring_index(STRING,'|',5),-1),
            NULL
           ),
       NULL) pay5,
  if ( STRING IS NOT NULL,
       f ( substring_index(STRING,'|',6) IS NOT NULL,
            substring_index(substring_index(STRING,'|',6),-1),
            NULL
           ),
       NULL) pay6
FROM ...


I did not run this vs a DB so please excuse syntax errors and if I
forgot some brackets.  But in principle it should work fine.

Cheers
/rudy


-----Original Message-----
From: Shazia Fazili [mailto:[EMAIL PROTECTED] 
Sent: maandag 14 juli 2003 19:35
To: Rudy Metzger
Subject: RE: Rows into Columns

Hi Rudy,
 
Thnaks for ur reply.  Your solution is adding up all Payments, while I
don't want all the Payments to be summed.  I want to show all Payments
against an InvoiceID. YOu see the result which I want my Query to
return.. it doesn't add up the payments

        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 am not adding Payments... 
         
        So what am I supposed to do now..
         
        Cheers,
        S


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]