Menu
I need something similar to these 2 SO questions, but using Informix SQL syntax.
Concatenate several fields into one with SQL
SQL Help: Select statement Concatenate a One to Many relationship
My data coming in looks like this:
id codes
63592 PELL
58640 SUBL
58640 USBL
73571 PELL
73571 USBL
73571 SUBL
I want to see it come back like this:
id codes
63592 PELL
58640 SUBL, USBL
73571 PELL, USBL, SUBL
See also group_concat() in Informix.
661
To implement a one-to-many relationship in the Teachers and Courses table, break the tables into two and link them using a foreign key. We have developed a relationship between the Teachers and the Courses table using a foreign key.
Click on the appropriate tool for the type of relationship you wish to create. If you are creating a one-to-many relationship, first click the table that is on the “many” side of the relationship, then on the table containing the referenced key. This creates a column in the table on the many side of the relationship.
In a one-to-one relationship, one record in a table is associated with one and only one record in another table. For example, in a school database, each student has only one student ID, and each student ID is assigned to only one person.
Oracle provides list aggregator function for such requirement.
SELECT id, LISTAGG(codes,',') as CODE_LIST FROM <TABLE> GROUP BY id
Output will be like
ID CODE_LIST
63592 PELL
58640 SUBL,USBL
73571 PELL,USBL,SUBL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
