Create concatenate SQL query for multiple rows

Question

We are trying to create a SQL query in Sybase where we can concatenate multiple rows into one selected row.

Layout:

| Type | Skill |
----------------
| A    |  1     
| A    |  2     
| B    |  1
ETC

I want the output to be like: A (1,2)

Answer 1

If using ASE 16 you may be able to roll your own function to simulate the list() function; see my responses in this thread: Implementing group_concat() in ASE

Pay attention to Edison's response about KBA 2260479 ... a bug when using table variables inside a transaction.

----------- per the suggestion to copy pertinent info from sap.com link ...

Referring to an example @ Percona example (sorry, not going to replicate that link, too; the examples attached to this post show the results of matching the percona examples anyway)

To mimic MySQL's GROUP_CONCAT() function in ASE we need to understand some issues/limitations/observations:

• ASE does not allow aggregate/UDFs (eg, passing a result set as an argument to a function)

• ASE does not allow defining a UDF with a table variable as an input parameter

• ASE does not allow the creation of a #temp table inside a UDF

• ASE 16 SP02+ DOES allow the creation/use of table variables inside a UDF

• ASE DOES allow the exec() construct inside a UDF

• the GROUP_CONCAT() argument consists of columns/strings to be appended, an 'order by' clause, and an optional SEPARATOR (see examples at the percona.com link - above); it's not too hard to see that the columns/strings + 'order by' clause are components of a SELECT query

• we can simulate passing of a data set to the UDF by instead passing a SQL/SELECT query that represents the desired dataset; this SQL/SELECT query can then be executed via the exec() construct to provide the UDF with the desired data set

---

The gist of our UDF design:

NOTE: Due to our use of a table variable the following requires ASE 16.0 SP02+

1 - the UDF's input parameter - @sql varchar(XXX) - represents a SQL/SELECT statement supplied by the calling process

1a- @sql must be a complete stand-alone query (ie, you should be able to run the query on its own in a separate ASE session)

1b - the select/projection list of @sql matches the column(s) of the table variable (see next bullet)

1c - @sql includes any necessary 'group/order by' clauses (ie, the UDF will not perform any sort operations)

2 - the UDF creates a table variable with a column defined to hold the results of the @sql query

2a - the column(s) of the table variable must match, datatype wise, with the select/projection list of the @sql query

3 - the UDF populates the table variable via the exec() construct:

    exec("insert @table_var " + @sql)

4 - the UDF uses a cursor to loop through the records in the table variable

4a - cursor does not have a 'order by' clause => assume rows are ordered, upon insert to the table variable, based on @sql

---

Some specifics about this particular UDF:

1 - I've created the UDF in the sybsystemprocs database and named it:

 sp_f_group_concat

1a - the 'sp_' prefix means the UDF can be executed from within any database

1b - the 'f_' string allows me to quickly/visually see that this is a function and not a system stored proc

2 - the UDF has been created with the assumption that the incoming SQL/SELECT query will have a select/projection list consisting of a single varchar(100) column

2a - the calling process will need to perform any necessary datatype casting (to char) and any concatenation of columns/strings

2b - the @sql input parameter has been defined as varchar(1000), and the @separator has been defined as varchar(10) with a default of a single comma (',')

2c - the owner of the UDF will need to revise the varchar() lengths based on what they expect to handle in their environment

3 - since the UDF cannot be sure TF:7703 (allow accumulating of data, by row, into a single @variable) is enabled, and the UDF does not perform any sorting, we'll use a cursor to step through the records in our table variable

4 - it's not apparent from the percona.com examples how MySQL's GROUP_CONCAT() function handles appending NULL's with non-NULL values (eg, is the NULL ignored? is the NULL converted to the empty string ''? is the NULL converted to the string 'NULL'?); net result is that the UDF owner/user may need to revisit the UDF and/or @sql design if they find NULL's are not being handled as desired

---

Hmmmm, can't attach files to stackoverflow posts? ok, so cut-n-paste it is ... yuck, not quite what the source looks like ...

++++++++++++++ sp_f_group_concat.sql

• UDF DDL

use sybsystemprocs
go
if object_id('sp_f_group_concat') is not null
    drop function sp_f_group_concat
go
create function sp_f_group_concat
(@sql       varchar(1000)
,@separator varchar(10) = NULL
)
returns varchar(1000)
as
/*
    sp_f_group_concat

    ASE implementation of MySQL's GROUP_CONCAT() function.

    See https://ideas.sap.com/D36082 for a discussion of this topic, along
        with some examples (as attachments to one of Mark's comments)

    Requirements/Assumptions
    ========================
    - ASE version must support a) user defined functions and b) table variables
    - @sql is a standalone query that generates a result set consisting of a single varchar column
    - @sql includes an 'order by' clause if needed (ie, this function does not attempt to order the results generated by @sql)

    History
    =======
    10/10/2016  Mark A. Parsons     Initial coding
*/
    set nocount on

    declare @string     varchar(100),
        @string_list    varchar(1000)

    -- default delimiter to ',' if not supplied

    select  @separator = isnull(@separator,',')

    -- create/populate @strings table

    declare @strings table (string varchar(100))

    exec("insert @strings " + @sql)

    -- assume TF:7703 is not enabled, so can't use a single SELECT to append to a @variable
    -- assume @sql has a 'order by' clause and that our cursor will pull from @strings in the same order

    declare string_cur cursor
    for
    select  string
    from    @strings
    for read only

    -- loop through @strings rows, appending individual strings to @string_list

    open string_cur

    fetch string_cur into @string

    while @@sqlstatus = 0
    begin
        select  @string_list = @string_list + case when @string_list is not NULL then @separator end + @string
        fetch string_cur into @string
    end

    close string_cur
    deallocate cursor string_cur

    -- send concatenated list of strings back to calling process

    return @string_list
go
grant execute on sp_f_group_concat to public
go

++++++++++++++ sp_f_group_concat.test1.sql

• percona.com examples for the group_c table
• first query shows using the default separator (a single comma)
• second query shows using a 3-char separator

use tempdb
go
set nocount on
go
/*
    reproduction of the MySQL/GROUP_CONCAT() examples from:

    https://www.percona.com/blog/2013/10/22/the-power-of-mysql-group_concat/
*/
if object_id('group_c') is not NULL
    drop table group_c
go
create table group_c
(parent_id  int NULL
,child_id   int NULL
)
go
insert group_c values (1,1)
insert group_c values (1,1)
insert group_c values (1,2)
insert group_c values (1,3)
insert group_c values (1,4)
insert group_c values (2,1)
insert group_c values (2,4)
insert group_c values (2,6)
insert group_c values (3,1)
insert group_c values (3,2)
insert group_c values (4,1)
insert group_c values (4,1)
insert group_c values (5,0)
go

-----

print "
******************************

QUERY # 1 : List of parents and associated children (default separator = ',')

******************************
"
select  parent_id,

    -- we know child_id_list is relatively narrow in this case
    -- so reduce the width of the output via left(--,20)

    left(   dbo.sp_f_group_concat("select   distinct
                        convert(varchar(100), child_id)
                        from    group_c
                        where   parent_id = " + convert(varchar, parent_id) + "
                        order by child_id"
                    , default
                    )
        ,20) as child_id_list
from    group_c
group by parent_id
order by parent_id

/*
    results should look like:

 parent_id   child_id_list
 ----------- --------------------
           1 1,2,3,4
           2 1,4,6
           3 1,2
           4 1
           5 0
*/
go

-----

print "
******************************

QUERY # 1 : List of parents and associated children (separator = ' - ')

******************************
"
select  parent_id,

    -- we know child_id_list is relatively narrow in this case
    -- so reduce the width of the output via left(--,20)

    left(   dbo.sp_f_group_concat("select   distinct
                        convert(varchar(100), child_id)
                        from    group_c
                        where   parent_id = " + convert(varchar, parent_id) + "
                        order by child_id"
                    , " - "
                    )
        ,20) as child_id_list
from    group_c
group by parent_id
order by parent_id

/*
    results should look like:

 parent_id   child_id_list
 ----------- --------------------
           1 1 - 2 - 3 - 4
           2 1 - 4 - 6
           3 1 - 2
           4 1
           5 0
*/
go

++++++++++++++ sp_f_group_concat.test2.sql

• percona.com examples using the engineers/customers/issues/workflow tables
• second query (nested GROUP_CONCAT() calls) uses a temp table to store intermediate results since a single-query solution was just too convoluted/complex

use tempdb
go
set nocount on
go
/*
    reproduction of the MySQL/GROUP_CONCAT() examples from:

    https://www.percona.com/blog/2013/10/22/the-power-of-mysql-group_concat/

    Assumptions
    ===========
    - ASE's identity column attribute generates the same results as
        MySQL's AUTO_INCREMENT column attribute
        - otherwise the auto-generated customer.id values won't
            match the manually entered issues.company_id values
        - otherwise the auto-generated engineers.id and issues.id
            values won't match the manually entered values for
            workflow's engineer_id/issue_id pairs
*/

if object_id('engineers') is not NULL
    drop table engineers
go
create table engineers
(id     smallint    identity
,e_name     varchar(30) not NULL
,e_surname  varchar(30) not NULL
,url        varchar(255)    not NULL
)
go
alter table engineers
add primary key (id)
go
insert engineers (e_name, e_surname, url) values ('Miguel', 'Nieto',    'https://www.percona.com/about-us/our-team/miguel-angel-nieto')
insert engineers (e_name, e_surname, url) values ('Marcos', 'Albe',     'https://www.percona.com/about-us/our-team/marcos-albe')
insert engineers (e_name, e_surname, url) values ('Valerii',    'Kravchuk', 'https://www.percona.com/about-us/our-team/valerii-kravchuk')
insert engineers (e_name, e_surname, url) values ('Michael',    'Rikmas',   'https://www.percona.com/about-us/our-team/michael-rikmas')
go

if object_id('customers') is not NULL
    drop table customers
go
create table customers
(id     smallint    identity
,company_name   varchar(30) not NULL
,url        varchar(255)    not NULL 
)
go
alter table customers
add primary key (id)
go
insert customers (company_name, url) values ('OT','http://www.ovaistariq.net/')
insert customers (company_name, url) values ('PZ','http://www.peterzaitsev.com/')
insert customers (company_name, url) values ('VK','http://mysqlentomologist.blogspot.com/')
insert customers (company_name, url) values ('FD','http://www.lefred.be/')
insert customers (company_name, url) values ('AS','http://mysqlunlimited.blogspot.com/')
insert customers (company_name, url) values ('SS','https://www.flamingspork.com/blog/')
go

if object_id('issues') is not NULL
    drop table issues
go
create table issues
(id     smallint    identity
,customer_id    smallint    not NULL
,description    text
)
go
alter table issues
add primary key (id)
go
insert issues (customer_id, description) values (1,'Fix replication')
insert issues (customer_id, description) values (2,'Help with installation of Percona Cluster')
insert issues (customer_id, description) values (3,'Hardware suggestions')
insert issues (customer_id, description) values (4,'Error: no space left')
insert issues (customer_id, description) values (5,'Help with setup daily backup by Xtrabackup')
insert issues (customer_id, description) values (6,'Poke sales about Support agreement renewal')
insert issues (customer_id, description) values (4,'Add more accounts for customer')
insert issues (customer_id, description) values (2,'Create Hot Fix of Bug 1040735')
insert issues (customer_id, description) values (1,'Query optimisation')
insert issues (customer_id, description) values (1,'Prepare custom build for Solaris')
insert issues (customer_id, description) values (2,'explain about Percona Monitoring plugins')
insert issues (customer_id, description) values (6,'Prepare access for customer servers for future work')
insert issues (customer_id, description) values (5,'Decribe load balancing for pt-online-schema-change')
insert issues (customer_id, description) values (4,'Managing deadlocks')
insert issues (customer_id, description) values (1,'Suggestions about buffer pool size')
go

if object_id('workflow') is not NULL
    drop table workflow
go
create table workflow
(action_id  int     identity
,engineer_id    smallint    not NULL
,issue_id   smallint    not NULL
)
go
alter table workflow
add primary key (action_id)
go
insert workflow (engineer_id, issue_id) values (1,1)
insert workflow (engineer_id, issue_id) values (4,2)
insert workflow (engineer_id, issue_id) values (2,3)
insert workflow (engineer_id, issue_id) values (1,4)
insert workflow (engineer_id, issue_id) values (3,5)
insert workflow (engineer_id, issue_id) values (2,6)
insert workflow (engineer_id, issue_id) values (3,7)
insert workflow (engineer_id, issue_id) values (2,8)
insert workflow (engineer_id, issue_id) values (2,9)
insert workflow (engineer_id, issue_id) values (1,10)
insert workflow (engineer_id, issue_id) values (3,11)
insert workflow (engineer_id, issue_id) values (2,12)
insert workflow (engineer_id, issue_id) values (2,13)
insert workflow (engineer_id, issue_id) values (3,14)
insert workflow (engineer_id, issue_id) values (1,15)
insert workflow (engineer_id, issue_id) values (1,9)
insert workflow (engineer_id, issue_id) values (4,14)
insert workflow (engineer_id, issue_id) values (2,9)
insert workflow (engineer_id, issue_id) values (1,15)
insert workflow (engineer_id, issue_id) values (3,10)
insert workflow (engineer_id, issue_id) values (4,2)
insert workflow (engineer_id, issue_id) values (2,15)
insert workflow (engineer_id, issue_id) values (4,8)
insert workflow (engineer_id, issue_id) values (4,4)
insert workflow (engineer_id, issue_id) values (3,11)
insert workflow (engineer_id, issue_id) values (1,7)
insert workflow (engineer_id, issue_id) values (3,7)
insert workflow (engineer_id, issue_id) values (1,1)
insert workflow (engineer_id, issue_id) values (1,9)
insert workflow (engineer_id, issue_id) values (3,4)
insert workflow (engineer_id, issue_id) values (4,3)
insert workflow (engineer_id, issue_id) values (1,5)
insert workflow (engineer_id, issue_id) values (1,7)
insert workflow (engineer_id, issue_id) values (1,4)
insert workflow (engineer_id, issue_id) values (2,4)
insert workflow (engineer_id, issue_id) values (2,5)
go

print "
******************************
QUERY # 1 : List of issues for each engineer
******************************
"
/*
    for display purposes we'll use left() to reduce column widths based on known max widths for the test data
*/

select  left(e.e_name + ' ' + e.e_surname, 20)                              as engineer,
    left(dbo.sp_f_group_concat("select  distinct
                        convert(varchar,w.issue_id) + ' (' + c.company_name + ')'

                        from    workflow    w,
                            engineers   e,
                            customers   c,
                            issues      i
                        where   w.engineer_id   = e.id
                        and w.issue_id  = i.id
                        and i.customer_id   = c.id
                        and e.id        = " + convert(varchar,e.id) + "
                        order by w.issue_id"
                    , ', ')
        , 80)                                           as 'issue (customer)'
from    workflow    w,
    engineers   e,
    customers   c,
    issues      i
where   w.engineer_id   = e.id
and w.issue_id  = i.id
and i.customer_id   = c.id
group by e.id 
order by e_name, e_surname

/*
    results should look like:

 engineer                       issue (customer)
 ------------------------------ --------------------------------------------------------------------------------
 Marcos Albe                    3 (VK), 4 (FD), 5 (AS), 6 (SS), 8 (PZ), 9 (OT), 12 (SS), 13 (AS), 15 (OT)
 Michael Rikmas                 2 (PZ), 3 (VK), 4 (FD), 8 (PZ), 14 (FD)
 Miguel Nieto                   1 (OT), 4 (FD), 5 (AS), 7 (FD), 9 (OT), 10 (OT), 15 (OT)
 Valerii Kravchuk               4 (FD), 5 (AS), 7 (FD), 10 (OT), 11 (PZ), 14 (FD)
*/
go

print "
******************************
QUERY # 2 : List of engineers for each customer (nested group_concat() calls)
******************************
"
/*
    while technically possible to nest our sp_f_group_concat() calls, the outer
        call becomes unwieldly since it will have to duplicate a copy of the inner
        call (and the full text for the e_list derived table) for each company;
        reason being that the e_list derived table has to be re-created for each
        outer call (per company)

    to make the code easier to read we're going to materialize the e_list derived table
        as a #temp table; for large data sets we'd want to look at the feasibilty of
        adding an index for performance reasons

    for display purposes we'll use left() to reduce column widths based on known
        max widths for the test data
*/

-- build/populate the #e_list table with a set of issue id's and associated engineer lists

if object_id('#e_list') is not NULL
    drop table #e_list
go
create table #e_list
(i_id       int
,engineer_list  varchar(1000)
)
go
insert  #e_list
select  i.id                                        as i_id,
    dbo.sp_f_group_concat("select   distinct
                    e.e_name + ' ' + e.e_surname
                from    workflow w,
                    engineers e,
                    issues i
                where   w.engineer_id   = e.id
                and w.issue_id  = i.id
                and i.id        = " + convert(varchar, i.id) + "
                order by e.e_name, e.e_surname"
                , ', ')                         as engineer_list
from    workflow w,
    engineers e,
    issues i
where   w.engineer_id   = e.id
and w.issue_id  = i.id
group by i.id
go

-- now run the main query to display isuses/engineer-lists by company

select  left(c.company_name, 10)                                    as company,
    left(dbo.sp_f_group_concat("select  distinct
                        convert(varchar,e_list.i_id) + ' (' + e_list.engineer_list + ')'
                    from    workflow    w,
                        engineers   e,
                        customers   c,
                        issues      i,
                        #e_list     e_list
                    where   w.engineer_id   = e.id
                    and w.issue_id  = i.id
                    and i.customer_id   = c.id
                    and w.issue_id  = e_list.i_id
                    and c.id        = " + convert(varchar, c.id) + "
                    order by w.issue_id"
                    , ', ' )
        , 140)                                          as issue
from    workflow    w,
    engineers   e,
    customers   c,
    issues      i,

    #e_list     e_list
where   w.engineer_id   = e.id
and w.issue_id  = i.id
and i.customer_id   = c.id
and w.issue_id  = e_list.i_id
group by c.id
order by c.company_name

/*
    results should look like:

 company    issue
 ---------- --------------------------------------------------------------------------------------------------------------------------------------------
 AS         5 (Marcos Albe, Miguel Nieto, Valerii Kravchuk), 13 (Marcos Albe)
 FD         4 (Marcos Albe, Michael Rikmas, Miguel Nieto, Valerii Kravchuk), 7 (Miguel Nieto, Valerii Kravchuk), 14 (Michael Rikmas, Valerii Kravchuk)
 OT         1 (Miguel Nieto), 9 (Marcos Albe, Miguel Nieto), 10 (Miguel Nieto, Valerii Kravchuk), 15 (Marcos Albe, Miguel Nieto)
 PZ         2 (Michael Rikmas), 8 (Marcos Albe, Michael Rikmas), 11 (Valerii Kravchuk)
 SS         6 (Marcos Albe), 12 (Marcos Albe)
 VK         3 (Marcos Albe, Michael Rikmas)
*/
go

++++++++++++++

NOTE: For the example queries you'll notice that the @sql string passed to the sp_f_group_concat() function is basically a copy of the parent query plus an additional 'where' clause to allow limiting the query to just the rows that match the parent query's 'group by' clause (ie, the additional 'where' clause matches the column(s) in the 'group by' clause)

Answer 2

Which specific type of Sybase database is this? if ASE, then you either have to use a loop approach (e.g. Cursor), or use a somewhat exotic trick with the UPDATE statement using local variables (too much to describe here quickly, but details are in my book 'tips, tricks and recipes for sybase ase'