Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

db2

How do I concatenate multiple rows into a single row using SQL? My database is DB2

TableFoo
 -------
 Id      Name
 1       Apples
 1       Tomatoes
 1       Potatoes
 2       Banana
 2       Peach

I want something like

ID       FruitsAvailable
-------------------------
 1       Apples, Tomatoes, Potatoes
like image 268
Zo Has Avatar asked Feb 13 '13 10:02

Zo Has


People also ask

How do I combine multiple rows of data into one row in SQL?

You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.

How do I select multiple rows in a single column in SQL?

In this case, we use GROUP_CONCAT function to concatenate multiple rows into one column. GROUP_CONCAT concatenates all non-null values in a group and returns them as a single string. If you want to avoid duplicates, you can also add DISTINCT in your query.


1 Answers

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 
like image 156
echo_Me Avatar answered Oct 20 '22 07:10

echo_Me