1

I'm using some SQL queries to extract data from a Oracle database, but one of the issues I keep having is duplicate rows because of unique data in each row. Example of this:

TEST    01-11-17    global  CURRENT 14-06-18    1474    AAXXZZ
TEST    01-11-17    global  CURRENT 14-06-18    1474    ZZXXAA
TEST    01-11-17    global  CURRENT 14-06-18    1474    XXZZAA

What I would like to achieve with this query:

TEST    01-11-17    global  CURRENT 14-06-18    1474    AAXXZZ, ZZXXAA, XXZZAA

The query is built as follow:

SELECT
    s.spacename "Space",
    s.creationdate "Created Date",
    s.spacetype "Space Type",
    s.spacestatus "Space Status",
    MAX(c.lastmoddate) "Last Modified Date",
    COUNT(*) "Space Page Count",
    u.username "Space Admins"
FROM
    content c,
    spaces s
    JOIN SPACEPERMISSIONS p ON s.SPACEID = p.SPACEID
    JOIN user_mapping u ON p.PERMUSERNAME = u.user_key
WHERE
    c.spaceid = s.spaceid
and
    c.contenttype='PAGE'
and
    prevver IS NULL
and
    p.PERMTYPE = 'SETSPACEPERMISSIONS'
GROUP BY
    s.spacename, u.username, s.creationdate, s.spacetype, s.spacestatus
ORDER BY
    spacename;

Thank you!

Danny
  • 189
  • 2
  • 15
  • 1
    https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle – jarlh Jun 14 '18 at 13:14
  • I tried it with a listagg, but then the following error occurs: SQL Error: ORA-01489: result of string concatenation is too long And with XMLAGG it's even not working.. – Danny Jun 14 '18 at 18:50

0 Answers0