CODEWITHSUNDEEP
sqlsql-servert-sql
C.Cam
C.Cam

Reputation: 65

SQL getting multiple values from the same id

I have a table in SQL that has multiple lines for the same id.

E.g:

ID     event_id     category_id   
1      1            3      
2      1            6
3      1            18
4      1            24
5      2            1

I am wondering how I would make an SQL query to achieve the output for each event_id it shows all the categories it is in.

E.g

{
    "event_id": 1,
    "categories": [3, 6, 18, 24]
}

Thanks

Upvotes: 0

Views: 1329

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82020

If by chance you're not on 2017+ (string_agg), here is a bit of a hack. I can't seem to find a way to create a simple json array.

Example

Declare @YourTable Table ([ID] int,[event_id] int,[category_id] int)  
Insert Into @YourTable Values 
 (1,1,3)
,(2,1,6)
,(3,1,18)
,(4,1,24)
,(5,2,1)


Select event_ID
      ,categories = JSON_QUERY('['+stuff((Select concat(',',category_id)  
                                            From  @YourTable 
                                            Where event_id=A.event_id 
                                            Order By event_id 
                                            For XML Path ('')),1,1,'')+']'
                              )
    From  @YourTable A
    Group By event_ID
    For   JSON Path

Returns

[{
    "event_ID": 1,
    "categories": [3, 6, 18, 24]
}, {
    "event_ID": 2,
    "categories": [1]
}]

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271191

Is string aggregation sufficient?

select event_id,
       string_agg(category_id, ',') within group (order by category_id)
from t
group by event_id;

Upvotes: 2

Related Questions