CODEWITHSUNDEEP
sqlself-join
user7146708
user7146708

Reputation: 197

sql combine 2 rows into one

I have the following dataset:

enter image description here

I am trying to convert the table on the left into the table on the right. I have several duplicates of orders with the same name but different products sold. I would like to combine the rows so it shows just one orderID. I've tried joining the table to itself based on order but I must be doing something wrong. Do you guys have any suggestions? this is probably super easy but I am not proficient with SQL yet. Thank you in advance.

Upvotes: 2

Views: 5679

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If there is at most one value in each column, you can use group by:

select order, name, max(product1) as product1, max(product2) as product2,
       max(product3) as product3
from lefttable
group by order, name;

That said, I suspect that the table on the left is the result of a query on the data. You probably simply need the right aggregation for that query.

Also, if you have more than one value in any column for an order, you can still do this, but the query is a bit more complicated.

Upvotes: 2

Related Questions