CODEWITHSUNDEEP
mysqlsql
Benjamin
Benjamin

Reputation: 1173

Transfer a multi column table into a simple form with SQL

Imagine a given table by the following form. For example from a system where someone can rent up to three books (as inventory-numbers)

table 'rentbooks':

name book1 book2 book3
Adam 100
Bob 200 250 400
Claire 300 350

I'd like to transfer this into a more useful form like this (1 book, 1 row), IDs don't matter:

name rentbookid
Adam 100
Bob 200
Bob 250
Bob 400
Claire 300
Claire 350

How could the Query be constructed to achieve that?

Upvotes: 0

Views: 30

Answers (1)

The Impaler
The Impaler

Reputation: 48800

You can use UNION ALL:

select name, book1 from rentbooks where book1 is not null
union all
select name, book2 from rentbooks where book2 is not null
union all
select name, book3 from rentbooks where book3 is not null

Result:

 name    book1 
 ------- ----- 
 Adam    100   
 Bob     200   
 Claire  300   
 Bob     250   
 Claire  350   
 Bob     400

See running example at DB Fiddle.

Upvotes: 3

Related Questions