377 votos

¿Cómo concatenar cadenas de un campo de cadena de un PostgreSQL ' grupo por ' consulta?

Estoy buscando una manera concatenar las cadenas de un campo dentro de un grupo de consulta. Por ejemplo, tengo una mesa:

ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave

y quería agrupar por company_id algo como:

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

Hay una función incorporada en mySQL para hacer este group_concat

573voto

Neall Puntos 12075

La actualización, como de PostgreSQL 9.0:

Las últimas versiones de Postgres (desde finales de 2010) los string_agg(expression, delimiter) función que va a hacer exactamente lo que la pregunta pide, incluso, permitiendo especificar el delimitador de cadena:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

La actualización, como de PostgreSQL 8.4:

PostgreSQL 8.4 (en 2009) ha introducido la función de agregado array_agg(expression) que concatena los valores en una matriz. A continuación, array_to_string() puede ser utilizado para dar el resultado deseado:

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

Respuesta Original (para pre-PostgreSQL 8.4):

No hay ninguna integrada en función de agregado para concatenar cadenas. Parece necesario, pero no es parte de la configuración por defecto. Una búsqueda en la web, sin embargo, revela algunas manual de implementaciones de la misma ejemplo:

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

Aquí está la CREATE AGGREGATE de la documentación.

Con el fin de obtener el ", " insertadas entre ellos sin tener a la final, es posible que desee crear su propia concatenación función y sustituirlo por el "textcat". Aquí está uno de los puse juntos, pero no las he probado (actualización: probado en 8.3.12 y funciona bien):

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

Nota: La función por encima de la salida de un coma, incluso si el valor de la fila es null/vacío, que salidas de:

a, b, c, , e, , g

Si prefiere eliminar el exceso de comas a la salida:

a, b, c, e, g

acaba de agregar un ELSIF cheque a la función:

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSIF instr IS NULL OR instr = '' THEN
      RETURN acc;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

103voto

Markus Döring Puntos 551

¿Uso de las funciones de matriz incorporada Postgres? Por lo menos en 8.4 funciona fuera de la caja:

SELECT company_id, array_to_string(array_agg(employee), ',')
FROM mytable
GROUP BY company_id;

20voto

dirbacke Puntos 440

A partir de PostgreSQL 9.0 puede utilizar la función de agregado llamada string_agg. Su nuevo SQL debe ser algo así:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

14voto

Guy C Puntos 930

Porque lo he encontrado después de buscar algún no reclamo ningún crédito para la respuesta:

Lo que no sabía es que PostgreSQL permite definir tus propias funciones de agregado con Crear agregados

Este post en la lista de PostgreSQL muestra cómo trivial es crear una función para hacer lo que se requiere:

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

SELECT company_id, textcat_all(employee || ', ')
FROM mytable
GROUP BY company_id;

7voto

bortzmeyer Puntos 12246

Como ya se mencionó, creando su propia función agregada es lo correcto a hacer. Aquí está mi función de agregado de concatenación (usted puede encontrar más información en francés):

CREATE OR REPLACE FUNCTION concat2(text, text) RETURNS text AS '
    SELECT CASE WHEN $1 IS NULL OR $1 = \'\' THEN $2
            WHEN $2 IS NULL OR $2 = \'\' THEN $1
            ELSE $1 || \' / \' || $2
            END; 
'
 LANGUAGE SQL;

CREATE AGGREGATE concatenate (
  sfunc = concat2,
  basetype = text,
  stype = text,
  initcond = ''

);

Y luego utilizarla como:

SELECT company_id, concatenate(employee) AS employees FROM ...

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X