daggett daggett - 7 months ago 16
SQL Question

Make column value as a row

I'm not sure I titled the question correctly, I'll try to explain what I mean by example.

I have the following query:

SELECT
d.name_ascii,
d.price,
t.whois_price
FROM domain d
JOIN tld t ON d.tld_id = t.id
JOIN user_tld ut ON t.id = ut.tld_id
WHERE ut.user_id = 1
AND d.name_ascii IN ('domain.com')


which returns the following:

+------------+---------+-------------+
| name_ascii | price | whois_price |
+------------+---------+-------------+
| domain.com | 321.000 | 15.000 |
+------------+---------+-------------+


It's ok, but I need next result:

+------------------+---------+
| item | price |
+------------------+---------+
| domain.com | 321.000 |
| domain.com whois | 15.000 |
+------------------+---------+


That is I need to remove 3-rd column named
whois_price
and insert it as another row with the same
price
value and concatenated
name_ascii
value with "whois" as
item
column.

I have no idea how to solve it. Any advice, hint?

Thanks!

Answer

One way to accomplish this would be with a UNION.

SELECT
  d.name_ascii as item,
  d.price
FROM domain d
WHERE d.name_ascii IN ('domain.com')
UNION ALL
SELECT
  concat(d.name_ascii,' whois'),
  t.whois_price
FROM domain d
  JOIN tld t ON d.tld_id = t.id
  JOIN user_tld ut ON t.id = ut.tld_id
WHERE ut.user_id = 1
  AND d.name_ascii IN ('domain.com')

The first part gets price, the second part gets the whois price. Here's a SQL Fiddle: http://sqlfiddle.com/#!9/d5164/3