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
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With