CODEWITHSUNDEEP
phpmysqlrow
Joe Lin
Joe Lin

Reputation: 23

MySQL Row to four Column

can any one help me on how can I create a query output using the row data on the source table as a header on my output. Please see below for illustration.

E.G.

Row Data:

+-----------+-----------+-----------+-----------+
| colHeader | value     | no        | price     |
+-----------+-----------+-----------+-----------+
| Header1   | value 1   | no 1      | price1    |
+-----------+-----------+-----------+-----------+
| Header1   | value 2   | no 2      | price2    |
+-----------+-----------+-----------+-----------+
| Header2   | value 1   | no 3      | price3    |
+-----------+-----------+-----------+-----------+
| Header2   | value 2   | no 4      | price4    |
+-----------+-----------+-----------+-----------+

Output:

+-----------+-----------+-----------+-----------+-----------+
| colHeader | value 1 no| value 2 no|value1price|value2price|
+-----------+-----------+-----------+-----------+-----------+
| Header1   | no 1      |    no 2   |price1     |   price2  |
+-----------+-----------+-----------+-----------+-----------+
| Header2   | no 3      |    no 4   |price3     |   price4  |
+-----------+-----------+-----------+-----------+-----------+

Is it possible?? I want print it through the PHP

Thank you.

Upvotes: 2

Views: 94

Answers (1)

fancyPants
fancyPants

Reputation: 51868

select
colHeader,
max(case when value = 'value 1' then no else null end) as value1,
max(case when value = 'value 2' then no else null end) as value2,
max(case when value = 'value 1' then price else null end) as value1price,
max(case when value = 'value 2' then price else null end) as value2price
from
your_table
group by colHeader

Executing the query in multiple steps is hopefully self explaining (added some notes, though):

select
*
from
t;

Result:

| COLHEADER |   VALUE |   NO |  PRICE |
|-----------|---------|------|--------|
|   Header1 | value 1 | no 1 | price1 |
|   Header1 | value 2 | no 2 | price2 |
|   Header2 | value 1 | no 3 | price3 |
|   Header2 | value 2 | no 4 | price4 |

select
colHeader,
case when value = 'value 1' then no else null end as value1,
case when value = 'value 2' then no else null end as value2,
case when value = 'value 1' then price else null end as value1price,
case when value = 'value 2' then price else null end as value2price
from
t;

Result:

| COLHEADER | VALUE1 | VALUE2 | VALUE1PRICE | VALUE2PRICE |
|-----------|--------|--------|-------------|-------------|
|   Header1 |   no 1 | (null) |      price1 |      (null) |
|   Header1 | (null) |   no 2 |      (null) |      price2 |
|   Header2 |   no 3 | (null) |      price3 |      (null) |
|   Header2 | (null) |   no 4 |      (null) |      price4 |

Not sure if the following step isn't more confusing than anything else. It returns the rows "collapsed" by column colHeader. That is, that a random row per group is displayed (in this case every second row is omitted), if no aggregate function like max() is used. Note, that this is not standard SQL and that if I'm not mistaken only MySQL allows this. Anyway, that's the reason, why we use the max() function in the last step.

select
colHeader,
case when value = 'value 1' then no else null end as value1,
case when value = 'value 2' then no else null end as value2,
case when value = 'value 1' then price else null end as value1price,
case when value = 'value 2' then price else null end as value2price
from
t
group by colHeader;

Result:

| COLHEADER | VALUE1 | VALUE2 | VALUE1PRICE | VALUE2PRICE |
|-----------|--------|--------|-------------|-------------|
|   Header1 |   no 1 | (null) |      price1 |      (null) |
|   Header2 |   no 3 | (null) |      price3 |      (null) |

Final step:

select
colHeader,
max(case when value = 'value 1' then no else null end) as value1,
max(case when value = 'value 2' then no else null end) as value2,
max(case when value = 'value 1' then price else null end) as value1price,
max(case when value = 'value 2' then price else null end) as value2price
from
t
group by colHeader;

Result:

| COLHEADER | VALUE1 | VALUE2 | VALUE1PRICE | VALUE2PRICE |
|-----------|--------|--------|-------------|-------------|
|   Header1 |   no 1 |   no 2 |      price1 |      price2 |
|   Header2 |   no 3 |   no 4 |      price3 |      price4 |

Upvotes: 4

Related Questions