Consultation with grouping

Question

Good morning gentlemen(s) I have the following problem: [Edited]

As can be seen in the image above, there are several fields with the same result (minus the "desric" column). What I need is that in addition to grouping the identical results, the desric column would look like this: "Domestic animals, noise";

Something like this:

|DESRIC|MEDPPIC|CONDFEPIC|USOIEPIC|PRZVEPIC|PERTEPIC|HIGEPIC|

|DOMESTIC ANIMALS|S|N|S|S|S|S|S|S|

|DOMESTIC ANIMALS, NOISE|S|S|S|S|S|S|S|S|

Sorry for the formatting, but I believe I can understand.

Follow my sql image [Edited]

----EDIT----

I ended up finding the command LIST(), which does exactly what I want, but I would still like to know from which version this function has been implemented.

Answer 1

Good morning. You can make a process to return a list and put it in your sql. Follow the example below:

CREATE OR ALTER procedure GET_LISTA (
    SQL_LISTA varchar(1000))
returns (
    LISTA varchar(4000))
as
declare variable ADD_LISTA varchar(100);
begin
  FOR EXECUTE STATEMENT SQL_LISTA INTO :ADD_LISTA DO
  BEGIN
    IF( LISTA IS NOT NULL ) THEN
    BEGIN
        LISTA = LISTA || ',' || ADD_LISTA;
    END
    ELSE
    BEGIN
        LISTA = ADD_LISTA;
    END
  END
  suspend;
end

SELECT DISTINCT
A.MEDPPIC,A.CONDFEPIC,A.USOIEPIC,A.PRZVEPIC,A.PERTEPIC,A.HIGEPIC,
(
 SELECT * 
 FROM GET_LISTA('SELECT DISTINCT C.DESCRIC FROM ESAEPIC A INNER JOIN ESACER B ON B.IDEPIC=A.IDEPIC INNER JOIN ESARIC C ON C.CODRIC=B.RICCER 
 WHERE A.MEDPPIC='||A.MEDPPIC||' AND A.CONDFEPIC='||A.CONDFEPIC||' AND A.USOIEPIC='||A.USOIEPIC||' AND A.PRZVEPIC='||A.PRZVEPIC||' AND A.HIGEPIC='||A.HIGEPIC)
) AS DESCRIC
FROM ESAEPIC A
INNER JOIN ESACER B ON B.IDEPIC=A.IDEPIC
INNER JOIN ESARIC C ON C.CODRIC=B.RICCER
WHERE A.IDEPIC IN(SELECT IDEPIC FROM ESACER WHERE CLCCER = :CLCCER)

I just didn’t test the sql because I don’t have the database structure and the data, but in my database with my data it worked.

Answer 2

Answering your question of when List was implemented:

• Added in version 2.1
• Highness in version 2.5

For more information you can see on firebird documentation

Smart to have helped