sql - Get rid from one column duplicate values in two column select

Question

So, I`ve got two columns t1.NAME and t2.ITEMS, for each neme there can be more than one item assigned to it, so I want to select it like:

| NAME | ITEMS |
  JOHN    1
          2
  BEN     4
          7
          3
  DAVE    5

P.s. if it helps, they are connected by t1.id = t2.names_id

Answer 1

This kind of operation should be done in presentation layer.

But if you insist you can use sth like:

SqlFiddleDemo

SELECT DISTINCT NAME,
      LISTAGG(Items,  chr(13)||chr(10)) WITHIN GROUP (ORDER BY 1) OVER (PARTITION BY Name) AS Items
FROM tab

change tab with subquery that produce output you get now.

The clue is to concatenate for every name corresponding Items and adding new line character CHR(13) + CHR(10).