Equivalent Function to Stuff in SQL (Group_Concat in Myssql/Listagg in Oracle)

Equivalent function to STUFF in SQL (GROUP_CONCAT in MySSQL / LISTAGG in Oracle)

It looks like you are in luck - Firebird 2.1 introduced a LIST() aggregate function which works like GROUP_CONCAT in MySQL, which allows a query like so:

SELECT p.Name, LIST(c.Name, ', ')
FROM parent p INNER JOIN child c on c.parentid = p.parentid
GROUP by p.Name;

Edit, re Ordering

You may be able to influence ordering by pre-ordering the data in a derived table, prior to applying the LIST aggregation function, like so:

SELECT x.ParentName, LIST(x.ChildName, ', ')
FROM 
(
  SELECT p.Name as ParentName, c.Name as ChildName
  FROM parent p INNER JOIN child c on c.parentid = p.parentid
  ORDER BY c.Name DESC
) x
GROUP by x.ParentName;

Aggregate function in MySQL - list (like LISTAGG in Oracle)

You're looking for GROUP_CONCAT()

Try this:

select group_concat(MyString separator ', ') as myList from table
where id < 4

Of course, you can group by the results.

ListAGG in SQLSERVER

Starting in SQL Server 2017 the STRING_AGG function is available which simplifies the logic considerably:

select FieldA, string_agg(FieldB, '') as data
from yourtable
group by FieldA

See SQL Fiddle with Demo

In SQL Server you can use FOR XML PATH to get the result:

select distinct t1.FieldA,
  STUFF((SELECT distinct '' + t2.FieldB
         from yourtable t2
         where t1.FieldA = t2.FieldA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,0,'') data
from yourtable t1;

See SQL Fiddle with Demo

How to Merge fields after query, i know CONCAT, but not this way

Here you go:

MySQL using group_concat:

select a.name,
    a.opcode,
    group_concat(month order by b.pk separator ', ') as months
from tablea a
join tableb b on a.opcode = b.opcode
group by a.name, a.opcode;

Oracle using listagg:

select a.name,
    a.opcode,
    listagg(month,', ') within group (order by b.pk) as months
from tablea a
join tableb b on a.opcode = b.opcode
group by a.name, a.opcode;

SQL Server using for xml path and stuff:

select a.*,
    stuff((
        select ', ' + month from tableb b
        where a.opcode = b.opcode
        order by pk 
        for xml path(''), type
        ).value('(./text())[1]', 'varchar(max)')
    , 1, 2, '') as months
from tablea a;

Spark SQL replacement for MySQL's GROUP_CONCAT aggregate function

Before you proceed: This operations is yet another another groupByKey. While it has multiple legitimate applications it is relatively expensive so be sure to use it only when required.

---

Not exactly concise or efficient solution but you can use UserDefinedAggregateFunction introduced in Spark 1.5.0:

object GroupConcat extends UserDefinedAggregateFunction {
    def inputSchema = new StructType().add("x", StringType)
    def bufferSchema = new StructType().add("buff", ArrayType(StringType))
    def dataType = StringType
    def deterministic = true 

    def initialize(buffer: MutableAggregationBuffer) = {
      buffer.update(0, ArrayBuffer.empty[String])
    }

    def update(buffer: MutableAggregationBuffer, input: Row) = {
      if (!input.isNullAt(0)) 
        buffer.update(0, buffer.getSeq[String](0) :+ input.getString(0))
    }

    def merge(buffer1: MutableAggregationBuffer, buffer2: Row) = {
      buffer1.update(0, buffer1.getSeq[String](0) ++ buffer2.getSeq[String](0))
    }

    def evaluate(buffer: Row) = UTF8String.fromString(
      buffer.getSeq[String](0).mkString(","))
}

Example usage:

val df = sc.parallelize(Seq(
  ("username1", "friend1"),
  ("username1", "friend2"),
  ("username2", "friend1"),
  ("username2", "friend3")
)).toDF("username", "friend")

df.groupBy($"username").agg(GroupConcat($"friend")).show

## +---------+---------------+
## | username|        friends|
## +---------+---------------+
## |username1|friend1,friend2|
## |username2|friend1,friend3|
## +---------+---------------+

You can also create a Python wrapper as shown in Spark: How to map Python with Scala or Java User Defined Functions?

In practice it can be faster to extract RDD, groupByKey, mkString and rebuild DataFrame.

You can get a similar effect by combining collect_list function (Spark >= 1.6.0) with concat_ws:

import org.apache.spark.sql.functions.{collect_list, udf, lit}

df.groupBy($"username")
  .agg(concat_ws(",", collect_list($"friend")).alias("friends"))

Oracle equivalent to SQL Server STUFF function?

You can write a custom aggregate function to do this. This string you generate is limited to 4k characters.

http://www.sqlsnippets.com/en/topic-11591.html

There is an undocumented, unsupported function WMSYS.WM_CONCAT to do the same thing.

http://www.psoug.org/reference/undocumented.html

Collecting into VARRAY inside SQL statement

Duplicate of this one:

If you have Oracle 10G:

CREATE OR REPLACE FUNCTION GET_COMMA_SEPARATED_VALUE ( INPUT_VAL IN NUMBER )
    RETURN VARCHAR2
IS
    RETURN_TEXT VARCHAR2 ( 10000 ) := NULL;
BEGIN
    FOR X IN ( SELECT
                  ID
            FROM
                  ACCOUNTS
            WHERE
                  OC_YEAR = INPUT_VAL )
    LOOP
        RETURN_TEXT :=
               RETURN_TEXT
            || ','
            || X.ID;
    END LOOP;

    RETURN LTRIM ( RETURN_TEXT,
                ',' );
END;
/

So, you can do like:

SELECT
      GET_COMMA_SEPARATED_VALUE ( ID ),
      OC_YEAR
FROM
      ACCOUNTS;

If you have got oracle 11g, you can use listagg :

SELECT
      LISTAGG ( OC_YEAR,
              ', ' )
      WITHIN GROUP (ORDER BY ID),
      OC_YEAR
FROM
      ACCOUNTS
GROUP BY
      OC_YEAR;

LISTAGG alternative in Oracle 10g

Try using XMLAGG like this:

select
    p.PeopleID,
    rtrim(xmlagg(xmlelement(e, s.SiteName, ',')).extract('//text()').getclobval(), ',')
from people p
join site s on p.SiteID = s.SiteID
group by p.PeopleID;

If you need the concatenation in a particular order, say increasing order of SiteId, then add an order by clause in the xmlagg:

select
    p.PeopleID,
    rtrim(xmlagg(xmlelement(e, s.SiteName, ',')
                   order by s.SiteId).extract('//text()').getclobval(), ',')
from people p
join site s on p.SiteID = s.SiteID
group by p.PeopleID;

EDIT:

If you want display result for all those people which are assigned to site 100:

select p.PeopleID,
    rtrim(xmlagg(
                xmlelement(e, s.SiteName, ',') order by s.SiteId
            ).extract('//text()').getclobval(), ',')
from people p
join site s on p.SiteID = s.SiteID
join (
    select distinct PeopleID
    from people
    where siteID = 1
    ) p2 on p.PeopleID = p2.PeopleID
group by p.PeopleID;

---

Related Topics