Problem in Fetching Result set in Desired Format 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 &amp; if that item is present i am showing it &amp; 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 .
I think you need a third table, orders,  which connects the items and persons.

Tables

CREATE TABLE IF NOT EXISTS `persons` (
  `person_id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`person_id`)
);

CREATE TABLE IF NOT EXISTS `items` (
  `item_id` int(4) NOT NULL AUTO_INCREMENT,
  `item_type` varchar(50) NOT NULL,
  PRIMARY KEY (`item_id`)
);

CREATE TABLE IF NOT EXISTS `orders` (
  `order_id` int(12) NOT NULL AUTO_INCREMENT,
  `item_id` int(4) NOT NULL,
  `person_id` int(4) NOT NULL,
  `status` varchar(50) NOT NULL,
  `ordered_date` date NOT NULL,
  PRIMARY KEY (`order_id`)
);


Data

INSERT INTO `persons` (`name`) VALUES
('Walter White'),
('John Locke');

INSERT INTO `items` (`item_type`) VALUES
('A'),
('B'),
('C');

INSERT INTO `orders` (`person_id`, `item_id`, `status`, `ordered_date`) VALUES
(1, 1, 'Y', '2012-5-2'),
(1, 2, 'N', '2012-5-5'),
(1, 3, 'Y', '2012-5-7'),
(1, 1, 'Y', '2012-5-17'),
(2, 1, 'Y', '2012-6-12'),
(2, 2, 'N', '2012-6-15');


Query

SELECT orders.person_id AS PID, GROUP_CONCAT(items.item_type) AS ITYPE, GROUP_CONCAT(status), GROUP_CONCAT(ordered_date)
FROM orders
INNER JOIN items ON
items.item_id = orders.item_id
GROUP BY person_id, orders.item_id


Result

| PID | ITYPE | GROUP_CONCAT(STATUS) | GROUP_CONCAT(ORDERED_DATE) |
-------------------------------------------------------------------
|   1 |   A,A |                  Y,Y |      2012-05-02,2012-05-17 |
|   1 |     B |                    N |                 2012-05-05 |
|   1 |     C |                    Y |                 2012-05-07 |
|   2 |     A |                    Y |                 2012-06-12 |
|   2 |     B |                    N |                 2012-06-15 |


SQL Fiddle