Rows into Coumns

[Nejibg]

Hi All,

I've a table that displays something like this:
clientid order OrderDate
-------- ------ ---------
1 Printer
2 mouse
1 monitor
3 printer
4 keyboard
2 keyboard

But I'd like to get this result:
Where I display the latest 4 orders in the same row.

clientid order1 order2 order3 order4
-------- ------- ------- ------ ------
1 printer monitor
2 mouse keyboard
3 printer
4 keyboard

Thanks for your help.

 

[spookie]

select * from <tablename> group by clientid ;

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[Nejibg]

Thanks for your reply.
That won't work as my clientid is alphanumric so it can be ab12 ... az20,...

Thanks again.

 

[hvass]

Spookie
Not sure this makes much sense if you use a group by would you not need some aggregate function like count, sum etc..

select * from <tablename> group by clientid;

would just give you some arbirary record

You can do a group by on char/varchar etc.. as well as numeric.

I would probably do it in the application/scripting language PHP perl or whatever... if you must do it in MySQL proceedure or code block you need to do something like this...

#set up the test data
use test;
drop table if exists t;
create table t (clientid int(8) unsigned not null, purch varchar(255), orddate date);
insert into t (clientid,purch,orddate) values
	(1,'printer','2008-01-01'),
	(2,'mouse','2008-01-01'),
	(1,'monitor','2008-02-01'),
	(3,'printer','2008-01-01'),
	(4,'keyboard','2008-01-01'),
	(2,'keyboard','2008-03-01');
select * from t;
select * from t group by clientid;

#rank the records in order date
drop temporary table if exists tx;
create temporary table tx (primary key(clientid,rank),rank int(8) unsigned auto_increment)
select * from t order by orddate;
select * from tx;

#aggreate to one per clientid, rank
drop temporary table if exists ty;
create temporary table ty
select
	clientid,
	if(rank=1,purch,'') as order1,
	if(rank=2,purch,'') as order2,
	if(rank=3,purch,'') as order3,
	if(rank=4,purch,'') as order4
from tx
group by clientid,rank;
select * from tx;

#ouptut in format requested
select
	clientid,
	max(order1) as order1,
	max(order2) as order2,
	max(order3) as order3,
	max(order4) as order4
from ty
group by clientid;

 

[Nejibg]

I'm not sure if this will work either.
I know I should have stated in my orignal post that my clientID is alphanumeric AB01...ZZ99

Thanks anyway.

 

[hvass]

Yes it will work with alphanumerrics like AB01..ZZ99, see my earlier post you can use group by with character data types.

 

[Nejibg]

Thanks for your reply.
I have to admit my SQL skills is not that good. I'll give it a test.

Cheers.