StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>In MySQL to convert rows into columns, you will have to apply an aggregate function along with a CASE expression.</p> <p>If you have a limited number of known values, then you could hard-code the query:</p> <pre><code>select a.id, a.ads_title, max(case when k.`key` ='KM' then v.value end) `KM`, max(case when k.`key` ='Year' then v.value end) `Year` from ads a left join ads_values v on a.id = v.ads_id left join ads_keys k on v.key_id = k.id where k.inlist = 1 group by a.id; </code></pre> <p>See <a href="http://sqlfiddle.com/#!2/8b74b/12" rel="noreferrer">SQL Fiddle with Demo</a>.</p> <p>Now if you want to alter the results based on the <code>inlist</code> value without having to rewrite the query, then you can use a prepared statement with dynamic SQL:</p> <pre><code>SET @sql = NULL; set @keyNumber = 1; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'max(case when k.`key` = ''', `key`, ''' then v.value end) as `', `key`, '`') ) INTO @sql FROM ads_keys where inlist = @keyNumber; SET @sql = CONCAT('SELECT a.id, a.ads_title, ', @sql, ' from ads a left join ads_values v on a.id = v.ads_id left join ads_keys k on v.key_id = k.id where k.inlist = ', @keyNumber, ' group by a.id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; </code></pre> <p>See <a href="http://sqlfiddle.com/#!2/8b74b/13" rel="noreferrer">SQL Fiddle with Demo</a>. Both versions will give a result:</p> <pre><code>| ID | ADS_TITLE | KM | YEAR | --------------------------------- | 1 | AAA | 89000 | 2010 | | 2 | BBB | 13000 | 2008 | | 3 | CCC | 79800 | 2008 | | 4 | DDD | 56781 | 2009 | | 5 | EEE | 10200 | 2000 | | 6 | FFF | 45877 | 2009 | </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload