0

Table has the following data: 

+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
|      1 | A        |        10 |
|      2 | B        |         3 |
|      3 | A        |         9 |
|      4 | C        |        40 |
|      5 | D        |         5 |
|      6 | A        |        14 |
|      7 | B        |        67 |
|      8 | A        |         8 |
+--------+----------+-----------+

Now I'd like to group results by itemname displaying each itemvalue in a separate column (suffixed by 1,2,3,4,5...), as below:

+----------+------------+------------+------------+------------+
| itemname | itemvalue1 | itemvalue2 | itemvalue3 | itemvalue4 |
+----------+------------+------------+------------+------------+
| A        |        10  |         9  |        14  |         8  |
| B        |         3  |        67  |            |            |
| C        |        40  |            |            |            |
| D        |         5  |            |            |            |
+----------+------------+------------+------------+------------+

A very similar question has been answered by @Mihai here MySQL - Rows to Columns, I like his answer most because it's dynamic and will hopefully return any number of itemvalues. What I really need is to modify his script slightly to group results by itemname, not by hostid.

This is the script he suggested. How can this be modified to get expected results by itemname? When changed to GROUP BY itemname I'm getting an error.

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when itemname = ''',
      itemname,
      ''' then itemvalue end),0) AS ',
      itemname
    )
  ) INTO @sql
FROM
  history;
SET @sql = CONCAT('SELECT hostid, ', @sql, ' 
                  FROM history 
                   GROUP BY hostid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;`
joell
  • 71
  • 1
  • 4
  • 18
  • See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Sep 15 '17 at 16:30
  • 1
    That said, seriously consider handling issues of data display in application code. – Strawberry Sep 15 '17 at 16:40
  • haha...good point, here is the Fiddle http://rextester.com/UGJ48916 – joell Sep 15 '17 at 16:43
  • The linked question is different. So you can't use that solution for your problem. – Paul Spiegel Sep 15 '17 at 16:59
  • Fascinating to hear, I knew I can't. Had I could one post less on SO. – joell Sep 15 '17 at 20:09

0 Answers0