Show a One to Many Relationship as 2 Columns - 1 Unique Row (Id & Comma Separated List)

Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list)

I believe that the answer you need is a user-defined aggregate, similar to this one:

CREATE FUNCTION gc_init(dummy VARCHAR(255)) RETURNING LVARCHAR;
    RETURN '';
END FUNCTION;

CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255))
    RETURNING LVARCHAR;
    IF result = '' THEN
        RETURN TRIM(value);
    ELSE
        RETURN result || ',' || TRIM(value);
    END IF;
END FUNCTION;

CREATE FUNCTION gc_comb(partial1 LVARCHAR, partial2 LVARCHAR)
    RETURNING LVARCHAR;
    IF partial1 IS NULL OR partial1 = '' THEN
        RETURN partial2;
    ELIF partial2 IS NULL OR partial2 = '' THEN
        RETURN partial1;
    ELSE
        RETURN partial1 || ',' || partial2;
    END IF;
END FUNCTION;

CREATE FUNCTION gc_fini(final LVARCHAR) RETURNING LVARCHAR;
    RETURN final;
END FUNCTION;

CREATE AGGREGATE group_concat
    WITH (INIT = gc_init, ITER = gc_iter,
          COMBINE = gc_comb, FINAL = gc_fini);

Given a table of elements (called elements) with a column called name containing (funnily enough) the element name, and another column called atomic_number, this query produces this result:

SELECT group_concat(name) FROM elements WHERE atomic_number < 10;

Hydrogen,Helium,Lithium,Beryllium,Boron,Carbon,Nitrogen,Oxygen,Fluorine

Applied to the question, you should obtain the answer you need from:

SELECT id, group_concat(codes)
    FROM anonymous_table
    GROUP BY id;

---

CREATE TEMP TABLE anonymous_table
(
    id      INTEGER NOT NULL,
    codes   CHAR(4) NOT NULL,
    PRIMARY KEY (id, codes)
);

INSERT INTO anonymous_table VALUES(63592, 'PELL');
INSERT INTO anonymous_table VALUES(58640, 'SUBL');
INSERT INTO anonymous_table VALUES(58640, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'PELL');
INSERT INTO anonymous_table VALUES(73571, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'SUBL');
INSERT INTO anonymous_table VALUES(73572, 'USBL');
INSERT INTO anonymous_table VALUES(73572, 'PELL');
INSERT INTO anonymous_table VALUES(73572, 'SUBL');

SELECT id, group_concat(codes)
    FROM anonymous_table
    GROUP BY id
    ORDER BY id;

The output from that is:

58640 SUBL,USBL
63592 PELL
73571 PELL,SUBL,USBL
73572 PELL,SUBL,USBL

The extra set of data was added to test whether insert sequence affected the result; it appears not to do so (the codes are in sorted order; I'm not sure whether there's a way to alter - reverse - that order).

---

Notes:

1. This aggregate should be usable for any type that can be converted to VARCHAR(255), which means any numeric or temporal type. Long CHAR columns and blob types (BYTE, TEXT, BLOB, CLOB) are not handled.
2. The plain LVARCHAR limits the aggregate size to 2048 bytes. If you think you need longer lengths, specify LVARCHAR(10240) (for 10 KiB), for example.
3. As of Informix 12.10.FC5, the maximum length that works seems to be 16380; anything longer seems to trigger SQL -528: Maximum output rowsize (32767) exceeded, which surprises me.

4. If you need to remove the aggregate, you can use:

   DROP AGGREGATE IF EXISTS group_concat;
   DROP FUNCTION IF EXISTS gc_fini;
   DROP FUNCTION IF EXISTS gc_init;
   DROP FUNCTION IF EXISTS gc_iter;
   DROP FUNCTION IF EXISTS gc_comb;
   

SQL Help: Select statement Concatenate a One to Many relationship

Assuming you're using SQL Server 2005:

This should do what you're after - obviously replace fields as you need:

For demo purposes, consider the following two table structures:

Students(
  STU_PKEY Int Identity(1,1) Constraint PK_Students_StuPKey Primary Key,
  STU_NAME nvarchar(64)
)

Courses(
  CRS_PKEY Int Identity(1, 1) Constraint PK_Courses_CrsPKey Primary Key,
  STU_KEY Int Constraint FK_Students_StuPKey Foreign Key References Students(STU_PKEY),
  CRS_NAME nvarchar(64)
)

Now this query should do the job you're after:

Select  s.STU_PKEY, s.STU_NAME As Student,
        Stuff((
            Select  ',' + c.CRS_NAME
            From    Courses c
            Where   s.STU_PKEY = c.STU_KEY
            For     XML Path('')
        ), 1, 1, '') As Courses 
From    Students s
Group By s.STU_PKEY, s.STU_NAME

Way simpler than the currently accepted answer...

Multiple rows to one comma-separated value in Sql Server

Test Data

DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)

Query

SELECT  ID
       ,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
         FROM @Table1 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID

Result Set

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

SQL Server 2017 and Later Versions

If you are working on SQL Server 2017 or later versions, you can use built-in SQL Server Function STRING_AGG to create the comma delimited list:

DECLARE @Table1 TABLE(ID INT, Value INT);
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400);


SELECT ID , STRING_AGG([Value], ', ') AS List_Output
FROM @Table1
GROUP BY ID;

Result Set

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

one to many relation with text parsing and sorting condition

(Would be a mess as a comment)

I don't know what you are trying to get as a result, but for a simple one you could do this:

select *
from tableA a
         left join (
    select regexp_split_to_table(poly_ids, ',') polyId, id_b, distance_m from tableB) b on b.polyId = a.id_a;

EDIT: Get the min per id_a (only those that have a distance_m):

select id_a, min(b.distance_m)
from tableA a
         inner join (
    select regexp_split_to_table(poly_ids, ',') polyId, id_b, distance_m from tableB) b on b.polyId = a.id_a
group by id_a;

SQL Unique Rows from single table

There's a complete implementation of GROUP_CONCAT for Informix in Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list). It will certainly handle the data you show.

How to show data from multiple rows into single row as comma separated string

Modification:
If your SQL Server version is 2017 or above then you can use string_agg() to achieve your desired result. It's way too much faster than stuff() with For XML PATH()

with cte as
(SELECT Books.Name BookName, Categories.Name CategoryName FROM Books_Categories 
    INNER JOIN Books ON Books.Id = Books_Categories.BookId
    INNER JOIN Categories ON Categories.Id = Books_Categories.CategoryId)

SELECT t.BookName,STRING_AGG(CategoryName,',')  CategoryName          
FROM cte t
GROUP BY t.BookName

You can achieve this in sql-server older than 2017 by using STUFF() with For XML PATH

Schema:

 CREATE TABLE [dbo].[books](
    [id] [int] NULL,
    [name] [varchar](50) NULL
 ) ON [PRIMARY]
 
 CREATE TABLE [dbo].[categories](
    [id] [int] NULL,
    [name] [varchar](50) NULL
 ) ON [PRIMARY]
 
 CREATE TABLE [dbo].[Books_Categories](
    [id] [int] NULL,
    [bookid] [int] NULL,
    [categoryid] [int] NULL
 ) ON [PRIMARY]

Insert statements:
insert into books values(1,'BookA');
insert into books values(2,'BookB');

 insert into categories values(1,'CategoryA');
 insert into categories values(2,'CategoryB');
 
 insert into Books_Categories values(1, 1,  1);
 insert into Books_Categories values(2, 1,  2);
 insert into Books_Categories values(3, 2,  2);

Query#1 (STUFF() and For XML PATH():

 with cte as
 (SELECT Books.Name BookName, Categories.Name CategoryName FROM Books_Categories 
     INNER JOIN Books ON Books.Id = Books_Categories.BookId
     INNER JOIN Categories ON Categories.Id = Books_Categories.CategoryId)
 
 SELECT t.BookName
       , STUFF(( SELECT ', ' + CategoryName
                 FROM cte 
                 WHERE BookName = t.BookName
                 FOR XML PATH(''),TYPE)
                 .value('.','NVARCHAR(MAX)'),1,2,'') AS CategoryName
 FROM cte t
 GROUP BY t.BookName
 GO

Query#2 (using string_agg() for SQL Server 2017(14x) and later):

 with cte as
 (SELECT Books.Name BookName, Categories.Name CategoryName FROM Books_Categories 
     INNER JOIN Books ON Books.Id = Books_Categories.BookId
     INNER JOIN Categories ON Categories.Id = Books_Categories.CategoryId)
 
 select t.bookname,string_agg(categoryname,',') CategoryName from cte t 
 GROUP BY t.BookName

Output:

---