Problem in Fetching Result set in Desired Format

Question

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 .

Answer 1

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

Answer 2

As you have not specified the programming language you are using, but in asp.net c# while printing your resulting dataset or data table you can formate your string,for eg;

// Create array of 5-tuples with population data for three U.S. cities, 1940-1950.

      Tuple<string, DateTime, int, DateTime, int>[] cities = 
      { Tuple.Create("Los Angeles", new DateTime(1940, 1, 1), 1504277, 
                     new DateTime(1950, 1, 1), 1970358),
        Tuple.Create("New York", new DateTime(1940, 1, 1), 7454995, 
                     new DateTime(1950, 1, 1), 7891957),  
        Tuple.Create("Chicago", new DateTime(1940, 1, 1), 3396808, 
                     new DateTime(1950, 1, 1), 3620962),  
        Tuple.Create("Detroit", new DateTime(1940, 1, 1), 1623452, 
                     new DateTime(1950, 1, 1), 1849568) };

  // Display header 
  string header = String.Format("{0,-12}{1,8}{2,12}{1,8}{2,12}{3,14}\n",
                                "City", "Year", "Population", "Change (%)");
  Console.WriteLine(header);
  string output;      
  foreach (var city in cities) {
     output = String.Format("{0,-12}{1,8:yyyy}{2,12:N0}{3,8:yyyy}{4,12:N0}{5,14:P1}",
                            city.Item1, city.Item2, city.Item3, city.Item4, city.Item5,
                            (city.Item5 - city.Item3)/ (double)city.Item3);
     Console.WriteLine(output);

The example displays the following output: City Year Population Year Population Change (%)

Los Angeles     1940   1,504,277    1950   1,970,358        31.0 % 
New York        1940   7,454,995    1950   7,891,957         5.9 % 
Chicago         1940   3,396,808    1950   3,620,962         6.6 % 
Detroit         1940   1,623,452    1950   1,849,568        13.9 %

Even you can loop your result set and in foreach loop of column you can use this string.format. e,g.

    foreach(datarow r in dt.rows)
    {
      foreach(column c in dt.columns)
       {
         system.console.writeline(String.Format("{0,-12}{1,8:yyyy}{2,12:N0}{3,8:yyyy}  {4,12:N0}{5,14:P1}",city.Item1, city.Item2, city.Item3, city.Item4, city.Item5,
                        (city.Item5 - city.Item3)/ (double)city.Item3));
        }
    }