Convert row to column in SQL

Question

Consider table with schema

orders(user int, amount int, created datetime);

With one row = transaction, and I want to query the transaction data group by user

e.g.

User    First_Order_Amount    First_Order_DateTime Second_OrderAmount    Second_Order_DateTime
1       21                    2019-01-02           22                    2019-01-03
2       13                    2019-01-04           14                    2019-01-05

SQL Fiddle: https://www.db-fiddle.com/f/v4gZUMFbuYorB27AH9yBKy/8

what about a "third_order", "seventh_order"... if you are not sure how many orders there might be, rows are better suited to represent your data. (Just a thought) — GameDroids, Mar 22 '19 at 09:09
Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Daniel E., Mar 22 '19 at 09:14
@P.Salmon my question is not about pivot table, I only need 1st and 2nd transactions — Ryan, Mar 22 '19 at 10:03

Amir Molaei · Answer 1 · 2019-03-22T12:39:27.727

1

If you are using MySQL 8+ you can also use window functions instead of group by:

SELECT user, amount, created FROM
(SELECT * ,
ROW_NUMBER() OVER (PARTITION BY user ORDER BY user) AS rn
from orders) t
where t.rn = 1

edited Mar 22 '19 at 12:39

answered Mar 22 '19 at 09:17

Amir Molaei

3,700
1
17
20

I simply do not understand how this could be upvoted. It doesn't even get the first order (and that's only part of what the question is asking). – Gordon Linoff Mar 22 '19 at 11:37
I totally missed the horizontal scroll bar, apparently the person who upvoted missed it too. – rad 6 mins ago Delete – Amir Molaei Mar 22 '19 at 12:53

score 0 · Answer 2 · answered Mar 22 '19 at 09:17

You can use nested query for this, e.g.:

SELECT o.user,
(SELECT o1.amount FROM orders o1 WHERE o1.user = o.user AND o1.created = (SELECT MIN(created) FROM orders WHERE user = o1.user)) AS first_order_amount,
(SELECT MIN(created) FROM orders o1 WHERE o1.user = o.user) AS first_order_datetime, 
(SELECT o1.amount FROM orders o1 WHERE o1.user = o.user AND o1.created = (SELECT MIN(created) FROM orders o1 WHERE o1.user = o.user AND o1.created > (SELECT MIN(created) FROM orders WHERE user = o1.user))) AS second_order_amount,
(SELECT MIN(created) FROM orders o1 WHERE o1.user = o.user AND o1.created > (SELECT MIN(created) FROM orders WHERE user = o1.user)) AS second_order_datetime
FROM orders o
GROUP BY o.user;

Here's the SQL Fiddle.

score 0 · Answer 3 · answered Mar 22 '19 at 11:36

In MySQL 8+, you can use window functions and conditional aggregation:

select o.user,
       max(case when seqnum = 1 then amount end) as first_order_amount,
        max(case when seqnum = 1 then created end) as first_order_datetime,
       max(case when seqnum = 2 then amount end) as second_order_amount,
       max(case when seqnum = 2 then created end) as second_order_datetime
from (select o.*,
             row_number() over (partition by user order by created) as seqnum
      from orders o
     ) o
group by o.user;

Convert row to column in SQL

3 Answers3