CODEWITHSUNDEEP
mysqlsqldatabaserecursive-queryunpivot
Tondo Ktbffh
Tondo Ktbffh

Reputation: 1

How to Unpivot Data in mysql

Help Guys, I Have Table

Table Product:

| id | product | quantity |
| 1  | tea     | 2        |
| 2  | juice   | 3        |

I want to Select use all of them to become:

| id | product |
| 1  | tea     |
| 1  | tea     |
| 2  | juice   |
| 2  | juice   |
| 2  | juice   |

How Can I Query it? thank you

Upvotes: 0

Views: 637

Answers (2)

Ketan Vekariya
Ketan Vekariya

Reputation: 352

Hello hope this will help you.

SELECT a.id , a.product , a.quantity 
FROM Product a 
cross join 
(select * from seq_1_to_10000) b 
where a.quantity >= b.seq

Upvote if you find useful.

Upvotes: 1

GMB
GMB

Reputation: 222582

One option uses a recursive query, available in MySQL 8.0:

with recursive cte as (
    select id, product, quantity from mytable
    union all
    select id, product, quantity - 1 from cte where quantity > 1
)
select id, product from cte

Upvotes: 1

Related Questions