Flatten records into columns in MySQL

Question

I want to flatten multiple records from a table as distinct columns in the result set. For ex:

select product_id, url from images where product_id = 1;

+-------------+-------------------------------+
| product_id  | url                           |
+-------------+-------------------------------+
|           1 | http://example.com/images/abc |
|           1 | http://example.com/images/def |
|           1 | http://example.com/images/ghi |
+-------------+-------------------------------+

I want the result set to have three columns something like this:

+-------------+---------------------------------+-------------------------------+-------------------------------+
| product_id  | Image 1                         | Image 2                       | Image 3                       | 
+-------------+---------------------------------+-------------------------------+-------------------------------+
|           1 | http://example.com/images/abc   | http://example.com/images/def | http://example.com/images/ghi |
+-------------+---------------------------------+-------------------------------+-------------------------------+

In case, we do not have three rows, I want the last columns to be null. For ex-

select product_id, url from images where product_id = 2;
+-------------+-------------------------------+
| product_id  | url                           |
+-------------+-------------------------------+
|           2 | http://example.com/images/abc |
|           2 | http://example.com/images/def |
+-------------+-------------------------------+

Then, the output should be:

+-------------+---------------------------------+-------------------------------+-------------------------------+
| product_id  | Image 1                         | Image 2                       | Image 3                       | 
+-------------+---------------------------------+-------------------------------+-------------------------------+
|           2 | http://example.com/images/abc   | http://example.com/images/def | NULL                          |
+-------------+---------------------------------+-------------------------------+-------------------------------+

In case of more than 3 rows, we can safely ignore more than 3 rows.

Any help would be greatly appreciated.

Answer 1

A correlated subquery might be the simplest approach:

select p.product_id,
       (select i.url
        from images i
        where i.product_id = p.product_id
        order by i.image_id
        limit 1 offset 0
       ) as url1,
       (select i.url
        from images i
        where i.product_id = p.product_id
        order by i.image_id
        limit 1 offset 1
       ) as url2,
       (select i.url
        from images i
        where i.product_id = p.product_id
        order by i.image_id
        limit 1 offset 2
       ) as url3
from products p;

This assumes you have a table called products. If not, you can always use (select distinct product_id from images) p.

This also assumes you have an image id column in images. If not, you can use order by url.

If you don't want to use the products table, you can do:

select p.product_id,
       (select i.url
        from images i
        where i.product_id = p.product_id
        order by i.image_id
        limit 1 offset 0
       ) as url1,
       (select i.url
        from images i
        where i.product_id = p.product_id
        order by i.image_id
        limit 1 offset 1
       ) as url2,
       (select i.url
        from images i
        where i.product_id = p.product_id
        order by i.image_id
        limit 1 offset 2
       ) as url3
from (select distinct product_id from images) p;

Answer 2

MySQL is pretty good at making things that should be easy much harder. This is still a PIVOT, but it's a little different in MySQL than T-SQL.

EDIT: We can replicate the ROW_NUMBER() functionality with the dynamic variables @rn to hold the row number and @pid to hold the previous row's product_ID, then CROSS APPLY that back to the images table. I use the rn value to correlate the product_ID records.

Product_ID 3 demonstrates that it will ignore any more than 3 image urls returned.

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE images (product_id int, url varchar(50) ) ;
INSERT INTO images 
SELECT 1,'http://example.com/images/abc' UNION ALL
SELECT 1,'http://example.com/images/def' UNION ALL
SELECT 1,'http://example.com/images/ghi' UNION ALL
SELECT 2,'http://example.com/images/abc' UNION ALL
SELECT 2,'http://example.com/images/def' UNION ALL
SELECT 3,'http://example.com/images/qrz' UNION ALL
SELECT 3,'http://example.com/images/rzq' UNION ALL
SELECT 3,'http://example.com/images/zqr' UNION ALL
SELECT 3,'http://example.com/images/qqq' UNION ALL
SELECT 3,'http://example.com/images/rrr' UNION ALL
SELECT 3,'http://example.com/images/zzz'
;

Query 1:

SELECT s1.product_ID
  , max( CASE WHEN s1.rn = 1 THEN s1.url END ) AS Image1
  , max( CASE WHEN s1.rn = 2 THEN s1.url END ) AS Image2
  , max( CASE WHEN s1.rn = 3 THEN s1.url END ) AS Image3
FROM (
  SELECT t1.product_ID, t1.url
    , @rn:=CASE WHEN @pid=product_ID THEN @rn+1 ELSE 1 END AS rn
    , @pid:=product_ID AS pid
  FROM images t1, ( SELECT @rn:=0,@pid:='' ) AS t
  ORDER BY t1.product_ID
) s1
GROUP BY s1.product_ID
ORDER BY s1.product_ID

Results:

| product_ID |                        Image1 |                        Image2 |                        Image3 |
|------------|-------------------------------|-------------------------------|-------------------------------|
|          1 | http://example.com/images/abc | http://example.com/images/def | http://example.com/images/ghi |
|          2 | http://example.com/images/abc | http://example.com/images/def |                        (null) |
|          3 | http://example.com/images/rrr | http://example.com/images/qqq | http://example.com/images/zqr |

Answer 3

This will sort out the records in url group by product_id. Then get the first three records per group_id.

select product_id,
       max(case when rank=1 then url end) as url_1,
       max(case when rank=2 then url end) as url_2,
       max(case when rank=3 then url end) as url_3
from (       
  select  a.product_id, 
        a.url,
        count(b.product_id)+1 as rank
  from  images a 
  left join images b 
  on a.product_id=b.product_id and a.url>b.url
  group by  a.product_id,
          a.url
   )  t
group by product_id
order by product_id;