group_concat in Informix
You would have to define a user-defined aggregate to do this. That has four parts - four functions (search for CREATE AGGREGATE in the IDS 12.10 Info Centre):
- Initializer (INIT)
- Iterator (ITER)
- Combine (COMBINE)
- Finalizer (FINAL)
That's the official terminology in capitals, and it is moderately intuitive. Think of calculating an average.
- Initializer: set sum = 0; N = 0
- Iterator: set sum += x; N++
- Combiner: set sum = sum1 + sum2; set N = N1 + N2
- Finalizer: result = sum / N -- with N=0 (zero-divide) checks
The combiner is used to combine intermediate results from parallel execution; each parallel execution starts with the iterator and generates intermediate results. When the parallel execution completes, the separate sets of values are combined with the combiner.
You can write analogous code in IDS - using stored procedures or C or Java UDRs.
See the SO question Show a one to many relationship as 2 columns — 1 unique row (ID & comma separated list) for a string-based GROUP_CONCAT() function implemented in Informix.
Informix - Aggregating list of values into comma-delimited string
I think the answer you need is given in these questions: SO 715350, SO 489081. It shows how to create and use a GROUP_CONCAT() aggregate that will do exactly what you want. The functionality is otherwise not available - that is, you have to add it to Informix, but it can (fairly) easily be added.
Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list)
I believe that the answer you need is a user-defined aggregate, similar to this one:
CREATE FUNCTION gc_init(dummy VARCHAR(255)) RETURNING LVARCHAR;
RETURN '';
END FUNCTION;
CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255))
RETURNING LVARCHAR;
IF result = '' THEN
RETURN TRIM(value);
ELSE
RETURN result || ',' || TRIM(value);
END IF;
END FUNCTION;
CREATE FUNCTION gc_comb(partial1 LVARCHAR, partial2 LVARCHAR)
RETURNING LVARCHAR;
IF partial1 IS NULL OR partial1 = '' THEN
RETURN partial2;
ELIF partial2 IS NULL OR partial2 = '' THEN
RETURN partial1;
ELSE
RETURN partial1 || ',' || partial2;
END IF;
END FUNCTION;
CREATE FUNCTION gc_fini(final LVARCHAR) RETURNING LVARCHAR;
RETURN final;
END FUNCTION;
CREATE AGGREGATE group_concat
WITH (INIT = gc_init, ITER = gc_iter,
COMBINE = gc_comb, FINAL = gc_fini);
Given a table of elements (called elements) with a column called name containing (funnily enough) the element name, and another column called atomic_number, this query produces this result:
SELECT group_concat(name) FROM elements WHERE atomic_number < 10;
Hydrogen,Helium,Lithium,Beryllium,Boron,Carbon,Nitrogen,Oxygen,Fluorine
Applied to the question, you should obtain the answer you need from:
SELECT id, group_concat(codes)
FROM anonymous_table
GROUP BY id;
CREATE TEMP TABLE anonymous_table
(
id INTEGER NOT NULL,
codes CHAR(4) NOT NULL,
PRIMARY KEY (id, codes)
);
INSERT INTO anonymous_table VALUES(63592, 'PELL');
INSERT INTO anonymous_table VALUES(58640, 'SUBL');
INSERT INTO anonymous_table VALUES(58640, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'PELL');
INSERT INTO anonymous_table VALUES(73571, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'SUBL');
INSERT INTO anonymous_table VALUES(73572, 'USBL');
INSERT INTO anonymous_table VALUES(73572, 'PELL');
INSERT INTO anonymous_table VALUES(73572, 'SUBL');
SELECT id, group_concat(codes)
FROM anonymous_table
GROUP BY id
ORDER BY id;
The output from that is:
58640 SUBL,USBL
63592 PELL
73571 PELL,SUBL,USBL
73572 PELL,SUBL,USBL
The extra set of data was added to test whether insert sequence affected the result; it appears not to do so (the codes are in sorted order; I'm not sure whether there's a way to alter - reverse - that order).
Notes:
- This aggregate should be usable for any type that can be converted to VARCHAR(255), which means any numeric or temporal type. Long CHAR columns and blob types (BYTE, TEXT, BLOB, CLOB) are not handled.
- The plain LVARCHAR limits the aggregate size to 2048 bytes. If you think you need longer lengths, specify
LVARCHAR(10240)
(for 10 KiB), for example. - As of Informix 12.10.FC5, the maximum length that works seems to be 16380; anything longer seems to trigger
SQL -528: Maximum output rowsize (32767) exceeded
, which surprises me. If you need to remove the aggregate, you can use:
DROP AGGREGATE IF EXISTS group_concat;
DROP FUNCTION IF EXISTS gc_fini;
DROP FUNCTION IF EXISTS gc_init;
DROP FUNCTION IF EXISTS gc_iter;
DROP FUNCTION IF EXISTS gc_comb;
have any SQL GROUP_CONCAT alternative function?
@dev5 Your code to hard to read
Expression #1 of SELECT list is not in GROUP BY clause and contains non aggregated column 'smartsaver.ud.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.
will be simply solved by changing the sql mode in MySQL by this command:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Thank You :-)
SQL Unique Rows from single table
There's a complete implementation of GROUP_CONCAT for Informix in Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list). It will certainly handle the data you show.
Setting string value through substring operator in Informix?
From the IBM Informix 14.10 documentation for the UPDATE SQL statement follow the links for the SET Clause and then Single-Column Format and Expression where there is a link to the Column Expressions. On that page is a description for the [first, last] syntax which is described as an Informix extension.
Here is an example:
create table tab1(mycolumn char(12));
insert into tab1 values("123456789abc");
update tab1 set mycolumn[7,10] = "ABCD";
select * from tab1;
with the result:
mycolumn
123456ABCDbc
Related Topics
How to Get a Wpf Datagrid to Save Changes Back to the Database
Postgres: What Is the Query 'Select * from User' Actually Doing
Enforce a Foreign-Key Constraint to Columns of Same Table
Rounding to 2 Decimal Places in SQL
Add a Column That Represents a Concatenation of Two Other Varchar Columns
Performing a Where - in Query in Couchdb
Inserting a Variable in a Raw SQL Query Laravel
SQL - Pivot Table and Group by Not Working
How to Force Oracle to Change a Query's Plan Without Using Hints
To Get Column Names from Table Having a Particular Value
Oracle Insert Failure:Not a Valid Month
Efficient Query to Split a Delimited Column into Separate Rows in Another Table
Generate a Sequential Number (Per Group) When Adding a Row to an Access Table
SQL Group by Day, Show Orders for Each Day
What Is the Equivalent of Xml Path and Stuff in Linq Lambda Expression (Group_Concat/String_Agg)