How to convert values from columns to rows

Order ID	Customer Name	Email Address	Product 1 - Brand	Product 1 - Description	Product 1 - Price	Product 2 - Brand	Product 2 - Description	Product 2 - Price
123	John Smith	johnsmith@gmail.com	Carrier	Inverter Airconditioning Unit	599	null	null	null
456	Jane Smith	j.smith@icloud.com	Lenovo	ThinkVision Monitor	149	Logitech	Ergonomic Mouse	79

I'm working with this kind of table/dataset that keeps a record of a customer's purchases but all of the products they bought are contained within a single row.

The table has 10 sets of these product brand, product description, product price, etc but what I would like to happen is to take the individual purchases from that single row and create subsequent rows with the details of the remaining products while still having the info of the customers.

There would be instances where a customer would only get one item so the rest of the columns would be empty. There are also other columns at the end of the table that has the total cost of their items, delivery address, etc that I also want to retain in the new rows that would be created.

What I want to achieve is something like this:

Order ID	Customer ID	Email Address	Product - Brand	Product - Description	Product - Price
123	John Smith	johnsmith@gmail.com	Carrier	Inverter Airconditioning Unit	599
456	Jane Smith	j.smith@icloud.com	Lenovo	ThinkVision Monitor	149
456	Jane Smith	j.smith@icloud.com	Logitech	Ergonomic Mouse	79

I'm uncertain how to go about this as this seems complicated. I know there's a similar function in Google Sheet where you transpose the cells but that only solves half of my problem. Any help/recommendation would be highly appreciated, thank you!

Solution

You can use multicolumn UNPIVOT operation in BigQuery.
For multicolumn unpivot we use form

UNPIVOT (multi_column_unpivot) [unpivot_alias]

There multi_column_unpivot is

    values_column_set
    FOR name_column
    IN (column_sets_to_unpivot)

values_column_set-> (brand,description,price)
column_sets_to_unpivot -> (brand1,description1,price1) ,(brand2,description2, price2),(brand3 ...)
You should include to this list all your table triples(brandN,descriptionN,priceN)

See example

with data as(
select 123 OrderID,'John Smith' as CustomerName,    'johnsmith@gmail.com' as EmailAddress,  'Carrier'  as Brand1, 'Inverter Airconditioning Unit' description1,599 price1   
    ,cast(null as string) Brand2,   cast(null as string) Description2,cast(null as integer) price2
  union all
select 456 OrderId, 'Jane Smith'CustomerName,   'j.smith@icloud.com' EmailAddress,'Lenovo' brand1,  'ThinkVision Monitor' description1, 149 price1
          , 'Logitech' brand2,  'Ergonomic Mouse' Description2, 79 price2

)
-- query
select OrderID, CustomerName, EmailAddress
  ,brand, description, price
from data
unpivot( (brand,description,price)  
       for col  in( (brand1,description1,price1) 
                   ,(brand2,description2,price2)
                  ) 
       )upvt;

Row	OrderID	CustomerName	EmailAddress	brand	description	price
1	123	John Smith	johnsmith@gmail.com	Carrier	Inverter Airconditioning Unit	599
2	456	Jane Smith	j.smith@icloud.com	Lenovo	ThinkVision Monitor	149
3	456	Jane Smith	j.smith@icloud.com	Logitech	Ergonomic Mouse	79