DB2 comma separated output


Answers

LISTAGG function is new function in DB2 LUW 9.7

see example:

create table myTable (id int, category int);

insert into myTable values (1, 1);
insert into myTable values (2, 2);
insert into myTable values (5, 1);
insert into myTable values (3, 1);
insert into myTable values (4, 2);

example: select without any order in grouped column

select category, LISTAGG(id, ', ') as ids from myTable group by category;

result:

CATEGORY  IDS
--------- -----
1         1, 5, 3
2         2, 4

example: select with order by clause in grouped column

select
  category,
  LISTAGG(id, ', ') WITHIN GROUP(ORDER BY id ASC) as ids
from myTable
group by category;

result:

CATEGORY  IDS
--------- -----
1         1, 3, 5
2         2, 4
Question

How to get inbuilt function for comma separated column values in sql in DB2 , e.g if there are columns with this policy id and it has 3 rows with the same id but have three different roles in three rows , then it should retrieve the rows in one row "3,4,5"

e.g.

1. 4555 "2"
2. 4555 "3"
3. 4555 "4"

output 4555 2,3,4 in a DB2 in one row




LISTAGG function is new function in DB2 LUW 9.7

see example:

create table myTable (id int, category int);

insert into myTable values (1, 1);
insert into myTable values (2, 2);
insert into myTable values (5, 1);
insert into myTable values (3, 1);
insert into myTable values (4, 2);

example: select without any order in grouped column

select category, LISTAGG(id, ', ') as ids from myTable group by category;

result:

CATEGORY  IDS
--------- -----
1         1, 5, 3
2         2, 4

example: select with order by clause in grouped column

select
  category,
  LISTAGG(id, ', ') WITHIN GROUP(ORDER BY id ASC) as ids
from myTable
group by category;

result:

CATEGORY  IDS
--------- -----
1         1, 3, 5
2         2, 4



SQL statement to summarize, aggregate and concatenate

If you are using MS Sql2005+ then this should work for you:

CREATE FUNCTION GetOrderNotes(@OrderNumber INT) 
RETURNS NVARCHAR(MAX) 
AS
BEGIN 

    DECLARE @ReturnText NVARCHAR(MAX)

    SET @ReturnText = ''


    IF EXISTS(SELECT * FROM Orders WHERE OrderNumber = @OrderNumber) 
    BEGIN 
        SELECT 
            @ReturnText = COALESCE(@ReturnText,'') + ISNULL(Note,'') + ', '
        FROM 
            Orders
        WHERE 
            OrderNumber = @OrderNumber

    END 


    RETURN @ReturnText

END


GO 


/*THEN JUST CALL THIS FUNCTION FROM WITHIN YOUR QUERY*/

SELECT 
    OrderNumber 
    , dbo.GetOrderNotes(OrderNumber) AS [Notes Combined] 
FROM 
    Orders
GROUP BY 
    OrderNumber 
ORDER BY 
    OrderNumber 

Obviously replace the table and column definitions as required but this should get you to where you need to.

I generally try and create functions for this sort of thing so that I don't have to keep writing out the same code/ can use it in multiple places. So if my requirements change then I can change it here and not have to change it in say half a dozen places.




How to concatenate multiple rows inside a single row in SQL?

try this

  SELECT id ,FruitsAvailable 
  FROM
      (SELECT id , group_concat(Name) as FruitsAvailable 
       FROM  TableFoo
       WHERE id = 1) t

HERE DEMO SQLFIDDLE

EDIT: in db2 you need to create function and then call it

 CREATE FUNCTION MySchema/MyUDF (
PARCol2 CHAR(5) )
RETURNS VARCHAR(1024)   
LANGUAGE SQL 
NOT DETERMINISTIC 
READS SQL DATA 
CALLED ON NULL INPUT 
DISALLOW PARALLEL 

 BEGIN 
  DECLARE ReturnVal VARCHAR(1024) NOT NULL DEFAULT '';

  FOR CsrC1 AS C1 CURSOR 
      FOR SELECT MyCol1 
             FROM MyTable 
             WHERE MyCol2 = ParCol2 
      DO SET ReturnVal = ReturnVal Concat CsrC1.MyCol1; 
  END FOR; 

  RETURN LTRIM(ReturnVal); 
 END  ; 

and then call it here

     Select  id, MyUDF(Name) as FruitsAvailable
     From TableFoo 
     where id = 1 



Tags