I have 2 tables having the following columns
Person ------ person_id, first_name, middle_name, last_name Items ----- person_item_id, person_id, item_type, status, ordered_date
The query is
select Person.person_id as PID, Group_concat(Items.item_type) AS ITYPE, Group_concat(Items.status) AS STATUS, Group_concat(Items.orderd_date)AS ODATE, Group_concat(Items.person_item_id) AS IID from Persons inner join Items ON Person.person_id = Items.person_id group by person_id;
The resultset returned is as follows
PID ITYPE STATUS ODATE IID 1 A,A,B,C Y,N,Y,Y 2/5/2012,5/5/2012,17/5/2012 1,1,2 2 A,B Y,N 5/5/2012,15/6/2012 1,2
One Person can have many item_type and can order same item many times,I want to show the record set in such a manner that no matter the item is purchased by person or not , if the item is present in the record set i am drawing a column and have to show the result corresponding to that as such if the item is purchase 3 times then i want to show the item and it's corresponding record i.e order_date,status etc in sequential manner.
For eg :-
If there are 3 items A, B,C then output should be shown on the screen as :-
PID A B C 1 Y N Y 2/5/2012 5/5/2012 7/5/2012 ----------- Y 17/5/2012 2 Y N 12/6/2012 15/6/2012
Now Suppose there is another item so what i am doing is first fetching the result set from above query and then traversing through that result set to check the existence of that item & if that item is present i am showing it & it's corresponding values , so 1st i have to traverse the whole result set to get the item_type and then have to show it .