SQL How Do I Display All Products for an Order In Separate Columns?-CodePudding

I'm working with a table in the following format

Order_ID	Product_Name
1	A
1	B
2	B
2	C
3	A
3	C
3	B

I need to query the data to output like this

Order_ID	Product_1	Product_2	Product_3
1	A	B
2	B	C
3	A	C	B

Basically, I need to show all, or at least several, product_names for a given order_id as separate columns. I found a few answers that suggested using self joins to do this, but that doesn't seem practical when I need to show more than two products.

This is in BigQuery.

CodePudding user response：

This general operation is called pivoting.

Depending on your database and its SQL dialect, there will be different optimal approaches for this. Snowflake and BigQuery, for example, have a PIVOT table operation that does specifically this.

On BigQuery:

with numbered as (
    select
        *,
        'Product_' || row_number() over (
            partition by OrderID order by ProductName asc
        ) as n
    from my_table
)
select
    *
from numbered pivot(
    max(ProductName) 
    for n in ('Product_1', 'Product_2', 'Product_3')
)
group by Order_ID

If your dialect doesn't support this, but does support window functions like row_number, then you can manually do something like this to support a pre-defined number of products:

with numbered as (
    select *, row_number() over (partition by OrderID order by ProductName asc) as n
    from my_table
)
select
    OrderID,
    max(case when n = 1 then ProductName end) as Product_1,
    max(case when n = 2 then ProductName end) as Product_2,
    ...
from numbered
group by Order_ID

CodePudding user response：

.. I need to show all, or at least several, product_names for a given order_id as separate columns

Consider below

select * from (
  select *, row_number() over(partition by Order_ID) index
  from your_table
)
pivot (any_value(Product_Name) Product for index in (1,2,3,4,5))

if applied to sample data in your question - output is