combine multiple rows with same ID into 1 row

Question

User-721945135 posted

Hi *,

I have 1 query and result display as following table:

ID	ProductsName	Suppliers
001	Pencil_1	ABC
001	Pencil_2	ABC
002	Ruler	XYZ

I want to combine ProductsName and Suppliers columns for duplicate ID and separate by comma, if the column has same result like Suppliers for ID=001, it will display once as following table:

ID	ProductsName	Suppliers
001	Pencil_1, Pencil_2	ABC
002	Ruler	XYZ

Please guide me how to solve it.

Thanks in advanced.

Answer 1

User-691759321 posted

This link is written for Microsoft SQL Server but it should help provide some general concepts that will work in Oracle.  The XML methods probably won't though.  Hope it helps!

http://www.projectdmx.com/tsql/rowconcatenate.aspx

Answer 2

User702049738 posted

if you are using oracle 11g, you could use listagg for that...see link below

http://www.oracle-developer.net/display.php?id=515

Answer 3

User364663285 posted

Hi *,

I have 1 query and result display as following table:

ID	ProductsName	Suppliers
001	Pencil_1	ABC
001	Pencil_2	ABC
002	Ruler	XYZ

I want to combine ProductsName and Suppliers columns for duplicate ID and separate by comma, if the column has same result like Suppliers for ID=001, it will display once as following table:

ID	ProductsName	Suppliers
001	Pencil_1, Pencil_2	ABC
002	Ruler	XYZ

Please guide me how to solve it.

Thanks in advanced.

1) you can have query to only select distinct supplier
2) select all ProductsName and concat them for the given supplier