CODEWITHSUNDEEP
sqlsql-serverstringt-sqlgroup-by
Zubair
Zubair

Reputation: 726

Combine multiple rows with similar values in one column but different values on other columns

I'm using SQL, trying to combine a table that looks like this:

+-----------------------------------+
| Name  | Cherries | Pears | Apples |
+-----------------------------------+
| User1 |    Yes   |   No  |   No   |
+-----------------------------------+
| User1 |    No    |   No  |   Yes  |
+-----------------------------------+
| User2 |    No    |   Yes |   No   |
+-----------------------------------+
| User2 |    No    |   No  |   Yes  |
+-----------------------------------+

Into a table that looks like this:

+-----------------------------------+
| Name  | Cherries | Pears | Apples |
+-----------------------------------+
| User1 |    Yes   |   No  |   Yes  |
+-----------------------------------+
| User2 |    No    |   Yes |   Yes  |
+-----------------------------------+

I can use GROUP BY to group them by Name but I don't know how to deal with the other columns.

Upvotes: 0

Views: 797

Answers (1)

GMB
GMB

Reputation: 222432

You can just use aggregation:

select
    name,
    max(cherries) cherries,
    max(pears)    pears,
    max(apples)   apples
from mytable
group by name

This works because, string-wise, 'Yes' is greater than 'No'. So if both values are present in a group, max() gives 'Yes'.

Upvotes: 2

Related Questions