asked
Remove possible duplicates
Hi, I have the following results: (Customer, Pet1 and Pet2 are the columns returned) Customer Pet1 Pet2 John Dog Cat John Dog What I need to show is only one row for this customer. Since John has a both a dog and a cat, I want to return only that row. But say he only had a dog and not a cat, then I would only want to return that row. Make sense? Thanks!
You're example is pretty close already, but would you ever see row combinations like this: John Cat John Cat Dog John Dog John Dog Cat If so, which column do you want Dog in and which column do you want Cat in? What if one of the rows contains a Fish and you suddenly have 3 pets in 2 columns? John Cat John Cat Dog John Dog John Dog Cat John Fish
each person could only have 1 pet or 2 pets. But not more than 2 pets. As for your top example, that could happen, but it would always only be the top 2 rows or only the bottom 2 rows.
Select the "max" pet from each column and group by customer... WITH bob AS ( SELECT 'John' AS Customer, 'Cat' AS Pet1, NULL AS Pet2 UNION ALL SELECT 'John', 'Cat', 'Dog' ) SELECT Customer, MAX(Pet1) AS Pet1, MAX(Pet2) AS Pet2 FROM bob GROUP BY Customer
Such a simple solution. I didn't even think to use the MAX function. That solved my problem. Many thanks!
15 People are following this question.
