locked
combine multiple rows with same ID into 1 row RRS feed

  • 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.
    Tuesday, December 6, 2011 11:20 AM

All replies

  • 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

    Tuesday, December 6, 2011 11:34 AM
  • 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

    Sunday, December 11, 2011 12:34 AM
  • 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

    Tuesday, December 13, 2011 2:38 AM