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 .