change table from rows to columns?

DKY

I don't know exactly how to describe what I'm trying to do. I have a table that has several columns, they are

item, location, onhandqty

I have about 20 different locations for each item and I want to change this to

item, location1onhand, location2onhand, location3onhand, location4onhand....

I have no idea how to do this. Does anyone have any suggestions?

dagon

i assume you mean output on screen in that format, in that case example 1 on mysql_fetch_assoc() shows you how

DKY

The ultimate goal is to display it on the screen but my concern is that this would be such a huge query (or set of queries) that it would bog down the web browser. Also, maybe I'm missing something but it looks to me like the link is a simple read from a database and display on the screen code. I'm looking for much more than that. What I'm looking to do is take a table that looks something like this (but for over 2Mil records):

item | location | onhandqty
abc | 1 | 5
abc | 2 | 21
abc | 3 | 6
abc | 4 | 13

and make it look like this:

item | location1onhand | location2onhand | location3onhand | location4onhand
abc | 5 | 21 | 6 | 13

(Hopefully this posts clearly)

My initial thought was to do a delete table and then insert for the first location and then an update query for each additional location after that but you would be looking at about 25 different queries...

DKY

If I were to go the way that I'm thinking with 20 + queries my insert query would look like

INSERT INTO tbltolasinventory2 ( TimeStamp, NAV_PN, AVL_781, INT_781, RIP_781 ) SELECT tbltolasstockfile.TimeStamp, tbltolasstockfile.NAV_PN, tbltolasstockfile.ON_HND, tbltolasstockfile.INTRANS, tbltolasstockfile.RIP_QTY FROM tbltolasstockfile WHERE (((tbltolasstockfile.WHSE)='781'));

and I have an update query that I'm playing with that seems to have updated some data but threw my server into a tizzy and I had to restart apache and mysql so I don't think it's the best way to do what I'm trying to do...

update tbltolasinventory2,tbltolasstockfile set tbltolasinventory2.AVL_770 = tbltolasstockfile.ON_HND where
tbltolasinventory2.NAV_PN = tbltolasstockfile.NAV_PN AND tbltolasstockfile.WHSE = '770';