MySQL equivalent for Oracle DECODE statement?

[teejay]

Is there a MySQL equivalent for Oracle's DECODE statement ? I am looking for code that will allow me to transform columns/rows within the SQL query.

My query returns data like this:

Date | Indicator | Name
---------------|----------------|---------------------------
01/01/2004| 1 | Mr Smith
01/01/2004| 2 | Mr Brown
02/01/2004| 1 | Mr Jones
02/01/2004| 2 | Mr White
02/01/2004| 3 | Mr Black

I would like to transform this to return as:

Date | 1 | 2 | 3
--------------- |--------------|--------------|--------------
01/01/2004 | Mr Smith | Mr Brown |
02/01/2004 | Mr Jones | Mr White | Mr Black

i.e. the values in the indicator column become the column headers.

Any ideas much appreciated.

[raf]

I think that GROUP_CONCAT() is the closest you get in mySQL.

SELECT `date`, GROUP_CONCAT(`Name`) as namelist FROM table GROUP BY `date`

would return

Date | namelist
--------------- |--------------|--------------|--------------
01/01/2004 | Mr Smith, Mr Brown |
02/01/2004 | Mr Jones, Mr White, Mr Black|

You can then split up the namelist value on the comma to get the names.

[Normann]

Although this is an old topic I think that it still may have an interrest. A general pattern for doing what teejay wanted - and not having the equivalent of the Oracle Decode - is to do the following:

select "Date", "1", "2", "3"
from dual
where 1 = 2
union
select date, name, "", ""
from tabel
where indicator="1"
union
select date, "", name, ""
from tabel
where indicator="2"
union
select date, "", "", name
from tabel
where indicator="3"
;

Assuming that "tabel" is the tabel to retrieve data from. Notice the use of the "dual" tabel - this is done in order for MySQL to return a faked column header for the result.

[Gremnebulin]

The MySQL equivalent to ORacles' DECODE() is ELT()

http://dev.mysql.com/doc/refman/4.1/en/string-functions.html#function_elt

[tagnu]

I don't think elt() is the correct function here.

There are 2 methods.

1. Using CASE.... THEN
http://www.ispirer.com/doc/sqlways38/Output/SQLWays-1-041.html

Sample,
select CASE job_lvl
WHEN 1 THEN 'level 1
WHEN 2 THEN 'level 2'
ELSE 'Unknown level' end
from employee where job_id>0 ;

2. Using IF()
http://www.experts-exchange.com/Database/MySQL/Q_20522525.html

Sample,
SELECT USERNAME, EMAIL, ID_NUMBER, DATE_REGISTERED, LAST_LOGIN,
IF(ACTIVE=1,'Yes','No') ACTIVE,
IF(ADMIN=1,'Yes','No') ADMIN
FROM MEMBERS