MySQL - Rows to Columns Query

Question

I have a SQL query question. My current table look like this :

  +--------+------+-----+-----+------+
  | hostid | itemname   |  itemvalue |
  +--------+------+-----+-----+------+
  |   A    |      1     |      10    |
  +--------+------+-----+-----+------+
  |   B    |      2     |      3     |
  +--------+------+-----+-----+------+

How would I write a query so that I can get an output like this ?

  +--------+------+-----+-----+--------+------+-----+----------+
  | itemname_A    | itemvalue_A   |  itemname_B  |  itemvalue_B|
  +--------+------+-----+-----+--------+------+-----+----------+
  |        1      |      10       |       2      |     3       |
  +--------+------+-----+-----+--------+------+-----+----------+

Although the duplicate is for sql server, the join approach should work on this case — Julio Daniel Reyes, Oct 22 '17 at 06:41
This question has been asked and answered to soooo many times here on SO. — Shadow, Oct 22 '17 at 06:46

score -1 · Accepted Answer · answered Oct 22 '17 at 06:43

-1

Using some aggregate function you can get the desired output

select 
max(case when hostid = 'A' then itemname end) itemname_A,
max(case when hostid = 'A' then itemvalue end) itemvalue_A,
max(case when hostid = 'B' then itemname end) itemname_B,
max(case when hostid = 'B' then itemvalue end) itemvalue_B
from demo

Note it will work for fixed values of hostid DEMO

answered Oct 22 '17 at 06:43

M Khalid Junaid

63,861
10
90
118

1

This worked perfectly! Thank you so much. It may have been a duplicate question, but thanks for taking the time to help with this. really appreciate it!! – collinsfm316 Oct 22 '17 at 07:07

MySQL - Rows to Columns Query

1 Answers1