0
   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.

Syed Asad Abbas Zaidi
  • 1,006
  • 1
  • 17
  • 32
  • are you storing comma separated values in columns? – e4c5 Dec 08 '16 at 07:31
  • 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 Dec 08 '16 at 07:34
  • Storing comma separated values is a **really**, really bad idea. –  Dec 08 '16 at 07: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) –  Dec 08 '16 at 07:38
  • http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 – e4c5 Dec 08 '16 at 07:41
  • to answer this question would be to do you a disservice – e4c5 Dec 08 '16 at 07:42
  • http://stackoverflow.com/questions/41095453/postgresql-query-to-split-the-array-into-rows – Syed Asad Abbas Zaidi Dec 12 '16 at 06:53

1 Answers1

0

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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 Dec 12 '16 at 05:03
  • http://stackoverflow.com/questions/41095453/postgresql-query-to-split-the-array-into-rows – Syed Asad Abbas Zaidi Dec 12 '16 at 06:53