CODEWITHSUNDEEP
t-sql
jwlford
jwlford

Reputation: 58

How to get unique values out of a GROUP BY clause

I have a table that has a unique identifier column, a relational key column, and a varchar column.

|  Id  |  ForeignId  |  Fruits  | 
--------------------------------- 
|  1   |      1      |  Apple   |  
|  2   |      2      |  Apple   |  
|  3   |      3      |  Apple   |  
|  4   |      4      |  Banana  |

What I would like to do, is group the data by the Fruits column, and also return a list of all the ForeignId keys that are in that particular group. Like so:

|  Fruit  |  ForeignKeys  |
---------------------------
|  Apple  |  1, 2, 3      |
|  Banana |  4            |

So far I have the SQL that I gets me the grouped Fruit values as that is trivial. But I cannot seem to find a good solution for retrieving the ForeignKeyId values that are contained within the group.

SELECT Fruits FROM FruitTable

I found the GROUP_CONCAT function in MySql which appears to do what I need but it doesn't seem to be available in SQL Server 2017.

Any help on this would be appreciated.

Upvotes: 0

Views: 121

Answers (2)

san
san

Reputation: 1515

If you are using SQL Server 2014 or older:

SELECT  Fruit       = Fruits 
    ,ForeignKeys    = STUFF(
                            (SELECT ',' + CAST(ForeignId AS VARCHAR(100))
                            FROM    FruitTable t2
                            WHERE   t1.Fruits = t2.Fruits 
                            FOR XML PATH ('')
                    )
                        ,1,
                1,
                ''
                ) 
FROM    FruitTable t1
GROUP   BY Fruits;

If you are using SQL Server 2016 or later, you can also write this simpler way:

SELECT  Fruit = Fruits, ForeignKeys = STRING_AGG(ForeignId, ',')
FROM    FruitTable
GROUP   BY Fruits;

Upvotes: 2

Abhishek Bag
Abhishek Bag

Reputation: 134

SELECT fruits,  foreignkeys = STUFF(
             (SELECT ',' + foreignid
              FROM fruittable t1
              WHERE t1.id = t2.id
              FOR XML PATH (''))
             , 1, 1, '') 
from fruittable 
group by fruits

This should work.

Upvotes: 0

Related Questions