Skip to content
Advertisement

Insert only selected field with multiple value into one value with delimiter

I have two tables, one is to record the queue, another one is record the data for each queue

Queue Table

Id Queue_Id
1 1

Data Table

Id Queue_Id Item Field
1 1 A Vege
2 1 B Fruit
3 1 A Vege
4 1 B Fruit

Now I have to create a stored procedure that will return a temp table in below format

Queue_Id Item Field
1 A Vege, Fruit
1 B Vege, Fruit

If I SELECT using String_AGG(), the Queue_Id will be also returning like this Queue_Id = (1,1), but I just want it to display only Queue_Id = (1)

Advertisement

Answer

You can use group by as follows:

Select d.queue_id, d.item,
       Max(Select String_agg(field,',') within group (order by field) 
          from (Select distinct field 
                  from data dd where dd.queue_id = d.queue_id) t) t ) as fields
  From data d
  Join queue q on d.queue_id = q.queue_id
 Where q.id = 1
 Group by queue_id, item
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement