unir - cómo concatenar subconsultas en un solo campo con sql server

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

Question

Estoy buscando una forma de concatenar las cadenas de un campo dentro de un grupo por consulta. Así, por ejemplo, tengo una tabla:

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

y quería agrupar por company_id para obtener algo como:

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

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

Answer 1

PostgreSQL 9.0 o posterior:

Las versiones recientes de Postgres (desde finales de 2010) tienen la función string_agg(expression, delimiter) que hará exactamente lo que la pregunta preguntó, incluso permitiéndole especificar la cadena del delimitador:

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

Postgres 9.0 también agregó la capacidad de especificar una cláusula ORDER BY en cualquier expresión agregada ; de lo contrario, el orden es indefinido. Así que ahora puedes escribir:

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

O de hecho:

SELECT string_agg(actor_name, '', '' ORDER BY first_appearance)

PostgreSQL 8.4 o posterior:

PostgreSQL 8.4 (en 2009) introdujo la función agregada array_agg(expression) que concatena los valores en una matriz. Luego, array_to_string() puede usarse para dar el resultado deseado:

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

string_agg para versiones anteriores a 9.0:

En caso de que alguien se encuentre con esto buscando una compatibilidad de compatibilidad para las bases de datos anteriores a la 9.0, es posible implementar todo en string_agg excepto la cláusula ORDER BY .

Así que con la siguiente definición, esto debería funcionar igual que en una base de datos 9.x Postgres:

SELECT string_agg(name, ''; '') AS semi_colon_separated_names FROM things;

Pero esto será un error de sintaxis:

SELECT string_agg(name, ''; '' ORDER BY name) AS semi_colon_separated_names FROM things;
--> ERROR: syntax error at or near "ORDER"

Probado en PostgreSQL 8.3.

CREATE FUNCTION string_agg_transfn(text, text, text)
    RETURNS text AS 
    $$
        BEGIN
            IF $1 IS NULL THEN
                RETURN $2;
            ELSE
                RETURN $1 || $3 || $2;
            END IF;
        END;
    $$
    LANGUAGE plpgsql IMMUTABLE
COST 1;

CREATE AGGREGATE string_agg(text, text) (
    SFUNC=string_agg_transfn,
    STYPE=text
);

Variaciones personalizadas (todas las versiones de Postgres)

Antes de 9.0, no había una función agregada incorporada para concatenar cadenas. La implementación personalizada más simple ( sugerida por Vajda Gabo en esta publicación de la lista de correo , entre muchas otras) es usar la función incorporada textcat (que se encuentra detrás del operador || ):

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

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

Esto simplemente une todas las cuerdas, sin separador. Para obtener un "," insertado entre ellos sin tenerlo al final, es posible que desee crear su propia función de concatenación y sustituirla por el "textcat" anterior. Aquí hay uno que armé y probé el 8.3.12:

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;

Esta versión generará una coma, incluso si el valor de la fila es nulo o está vacío, por lo que obtendrás una salida como esta:

a, b, c, , e, , g

Si prefiere eliminar las comas adicionales para generar esto:

a, b, c, e, g

Luego, agregue un cheque ELSIF a la función como esta:

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;

Answer 2

¿Qué hay de usar las funciones de matriz incorporadas de Postgres? Al menos en 8.4 esto funciona fuera de la caja:

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

Answer 3

A partir de PostgreSQL 9.0 puede usar la función agregada llamada string_agg . Tu nuevo SQL debería verse así:

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

Answer 4

Como ya se mencionó, crear su propia función agregada es lo correcto. Aquí está mi función agregada de concatenación (puede encontrar detalles 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 usarlo como:

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

Answer 5

De acuerdo con la versión PostgreSQL 9.0 y superior, puede usar la función agregada llamada string_agg. Tu nuevo SQL debería verse así:

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

Answer 6

Encontré útil esta documentación de PostgreSQL: http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html .

En mi caso, busqué SQL simple para concatenar un campo con corchetes, si el campo no está vacío.

select itemid, 
  CASE 
    itemdescription WHEN '''' THEN itemname 
    ELSE itemname || '' ('' || itemdescription || '')'' 
  END 
from items;

Answer 7

Este último fragmento de lista de anuncios podría ser interesante si va a actualizar a 8.4:

Hasta que 8.4 salga con uno super nativo, puede agregar la función array_accum () en la documentación de PostgreSQL para enrollar cualquier columna en una matriz, que luego puede ser utilizada por el código de la aplicación, o combinada con array_to_string () para formatear como una lista:

http://www.postgresql.org/docs/current/static/xaggr.html

Me vincularía a los documentos de desarrollo 8.4, pero parece que aún no muestran esta función.

Answer 8

No reclamo crédito por la respuesta porque la encontré después de algunas búsquedas:

Lo que no sabía es que PostgreSQL le permite definir sus propias funciones agregadas con CREATE AGGREGATE

Esta publicación en la lista de PostgreSQL muestra lo trivial que 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;

Answer 9

Siguiendo la respuesta de Kev, utilizando los documentos de Postgres:

Primero, cree una matriz de los elementos, luego use la función incorporada array_to_string .

CREATE AGGREGATE array_accum (anyelement)
(
 sfunc = array_append,
 stype = anyarray,
 initcond = ''{}''
);

select array_to_string(array_accum(name),''|'') from table group by id;

Answer 10

Siguiendo una vez más el uso de una función agregada personalizada de concatenación de cadenas: debe recordar que la instrucción de selección colocará las filas en cualquier orden, por lo que deberá hacer una selección secundaria en la instrucción de con una cláusula de orden por , y luego una selección externa con una cláusula por grupo para agregar las cadenas, por lo tanto:

SELECT custom_aggregate(MY.special_strings)
FROM (SELECT special_strings, grouping_column 
        FROM a_table 
        ORDER BY ordering_column) MY
GROUP BY MY.grouping_column

Answer 11

También puede utilizar la función de formato. Lo que también puede encargarse implícitamente de la conversión de texto de texto, int, etc. por sí mismo.

create or replace function concat_return_row_count(tbl_name text, column_name text, value int)
returns integer as $row_count$
declare
total integer;
begin
    EXECUTE format(''select count(*) from %s WHERE %s = %s'', tbl_name, column_name, value) INTO total;
    return total;
end;
$row_count$ language plpgsql;


postgres=# select concat_return_row_count(''tbl_name'',''column_name'',2); --2 is the value