title: MySQL - multi-row by 1 id & converting multi-column data into a comma delimited row
body: <p>I have a database table:<br>
  requesting persons ID column that separates each request (reqid)<br>
  category ID column that separates into different categories each item that's in the request (catid)<br>
  an item ID column that separates each item type within the category (itemid)<br>
  an item name column that lists the name of the item (item)<br>
  an item  descriptor column that gives a description of the item (desc)<br>
  and amount column that gives the total cost of each item in that category (amount)<br></p>
  
  <p>so my table looks like this:</p>
  
  <pre><code>|---------|-------|----------|--------|-------|------------|
  |  reqid  | catid | itemid   | item   | desc  | amount     | 
  |---------|-------|----------|--------|-------|------------|
  |  1      | 3     | 16       | food   | food  | 200        | 
  |---------|-------|----------|--------|-------|------------|
  |  1      | 3     | 17       | water  | wtr   | 50         | 
  |---------|-------|----------|--------|-------|------------|
  |  1      | 3     | 18       | film   | film  | 20         | 
  |---------|-------|----------|--------|-------|------------|
  |  1      | 5     | 30       | room   | room  | 500        | 
  |---------|-------|----------|--------|-------|------------|
  |  1      | 5     | 31       | chair  | chair | 150        | 
  |---------|-------|----------|--------|-------|------------|
  |  2      | 3     | 16       | food   | food  | 200        | 
  |---------|-------|----------|--------|-------|------------|
  |  2      | 3     | 17       | water  | wtr   | 50         | 
  |---------|-------|----------|--------|-------|------------|
  |  3      | 3     | 18       | film   | film  | 20         | 
  |---------|-------|----------|--------|-------|------------|
  |  3      | 5     | 30       | room   | room  | 500        | 
  |---------|-------|----------|--------|-------|------------|
  |  3      | 5     | 31       | chair  | chair | 150        | 
  |---------|-------|----------|--------|-------|------------|
  </code></pre>
  
  <p>And i want my query result to look like</p>
  
  <pre><code>|--------|------------------|----------------------|--------------------|--------------------|
  | reqid  | catid3itemid     | catid3item           | catid3desc         | catid3amount       | 
  |--------|------------------|----------------------|--------------------|--------------------|
  |  1     |16, 17, 18        | food, water, film    | food, wtr, film    | 200, 50, 20        | 
  |--------|------------------|----------------------|--------------------|--------------------|
  |  2     |16, 17            | food, water          |food, wtr           | 200, 50,           | 
  |--------|------------------|----------------------|--------------------|--------------------|
  |  3     |18                | film                 | film               | 20                 | 
  |--------|------------------|----------------------|--------------------|--------------------|
  </code></pre>
  
  <p>continued:</p>
  
  <pre><code>|------------------|----------------------|--------------------|--------------------|
  | catid4itemid     | catid4item           | catid4desc         | catid4amount       | 
  |------------------|----------------------|--------------------|--------------------|
  |                  |                      |                    |                    | 
  |------------------|----------------------|--------------------|--------------------|
  |                  |                      |                    |                    | 
  |------------------|----------------------|--------------------|--------------------|
  |                  |                      |                    |                    | 
  |------------------|----------------------|--------------------|--------------------|
  </code></pre>
  
  <p>continued final:</p>
  
  <pre><code>|------------------|----------------------|--------------------|--------------------|
  | catid5itemid     | catid5item           | catid5desc         | catid5amount       | 
  |------------------|----------------------|--------------------|--------------------|
  |30,31             |room, chair           |room, chair         | 500, 150           | 
  |------------------|----------------------|--------------------|--------------------|
  |                  |                      |                    |                    | 
  |------------------|----------------------|--------------------|--------------------|
  |30,31             |room, chair           |room, chair         | 500, 150           | 
  |------------------|----------------------|--------------------|--------------------|
  </code></pre>
  
  <p>I've been seeing posts about using
  GROUP_CONCAT or CROSS APPLY or SWITCH STATEMENT</p>
  
  <p>For instance GROUP_CONCAT:</p>
  
  <pre><code>select *, GROUP_CONCAT(`table`.`categoryid` ORDER BY `table`.`categoryid` ASC SEPARATOR ', ') AS `categoryid`
  from `table` 
  GROUP BY `table`.`requestid`
  </code></pre>
  
  <p>but this lumps everything into a column not separated by the category id , but all the category ids together in one column.  </p>
  
  <p>Any suggestions or help would be much appreciated.  </p>