Postgresql SQL View: Complex Structure

Question

   id Event No  BrandId  
    1   10           B2            
    2   10           B8            
    3   11           B1         
    4   13           B9         
    5   13           B3

I want the above table as a resultant from a SQL View query.

For Instance Brand Id matched are B2 and B8 for Event 10, therefore 2 records are created for it.

Just want to know how can I create records with number of brands matched.

Yes.... But I want the result to be as the 1st table mentioned in my question.... means a separate record for each brand id matched with an event — Syed Asad Abbas Zaidi
– Syed Asad Abbas Zaidi, Commented Dec 8, 2016 at 7:34
Storing comma separated values is a really, really bad idea. — user330315
– user330315, Commented Dec 8, 2016 at 7:35
Please post a complete example of the input data, the expected output and the definition of the tables (and views) involved. The multiple levels of unnesting aggregating and casting to an array seem overly complicated in your statement - it's hard to understand what you are trying to accomplish with that (and thus to suggest a better solution) — user330315
– user330315, Commented Dec 8, 2016 at 7:38

Erwin Brandstetter · Accepted Answer · 2025-02-06 13:46:53Z

1

It seems like you want a simple aggregation:

SELECT "Event No", array_agg("BrandId") AS brand_array
FROM   tbl
GROUP  BY "Event No"
ORDER  BY "Event No";  -- optional

Or string_agg("BrandId", ', ') AS brand_list.
Read about aggregate functions in the manual.

edited Feb 6 at 13:46

answered Dec 8, 2016 at 16:17

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Syed Asad Abbas Zaidi Over a year ago

In my question the table is "Event" and the Brand Id is calculated on a sub-query. Therefore for a single event more than one brand id could be matched. Hence I need to fire my sql query on table event and make number of records to the number of brand ids matched. I don't think the solution you provided is what exactly I needed.

Syed Asad Abbas Zaidi Over a year ago

stackoverflow.com/questions/41095453/…

Collectives™ on Stack Overflow

Postgresql SQL View: Complex Structure

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related