MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Rudy Metzger Date:July 14 2003 9:25am
Subject:RE: columns to rows
View as plain text  
IF the dates are limited and can be agreed upon before running the
kwiri, you can use:

SELECT no,
       IF (date=d1, data, NULL) d1,
       IF (date=d2, data, NULL) d2,
       IF (date=d3, data, NULL) d3
  FROM table
 GROUP BY no;

It will also NOT work if one date can contain multiple data, e.g.

No   data   date
1    uyt     d1
1    abc     d1

cheers
/rudy

-----Original Message-----
From: Phil Evans [mailto:pevans@stripped] 
Sent: vrijdag 11 juli 2003 17:12
To: mysql@stripped
Subject: columns to rows

Hi there. I am a rank amateur at this trying to make sense out of a heap
(and growing) of data.

I have a resultset with this structure:

no    data    date
1    uyt    d1
1    klh    d2
1    oiu    d3
2    kjh    d1
2    kljh    d2
2    asd    d3

that I wish to convert to this structure.

no     d1    d2    d3
1     uyt    klh    oiu
2     kjh    kljh   asd 

Given that the original has over 100,000 records, I was hoping to find
some reasonable way of doing it.

Thanking you,

PhilE

Thread
columns to rowsPhil Evans11 Jul
  • Re: columns to rowsharm11 Jul
    • Re: columns to rowsEmile State11 Jul
  • Re: columns to rowsVictoria Reznichenko11 Jul
RE: columns to rowsRudy Metzger14 Jul