【问题】
I am writing an oracle 10g query for creating jasper reports.
This is the query –
SELECT essay_id,
LTRIM (
MAX (SYS\_CONNECT\_BY\_PATH (full\_name, ','))
KEEP (DENSE_RANK LAST ORDER BY curr),
',')
AS full_name
FROM (SELECT essay_id,
full_name,
ROW\_NUMBER () OVER (PARTITION BY essay\_id ORDER BY full_name)
AS curr,
ROW_NUMBER ()
OVER (PARTITION BY essay\_id ORDER BY full\_name)
\- 1
AS prev
FROM (SELECT a.id AS essay_id,
CASE NVL (firstname, 'NULL FIRSTNAME')
WHEN 'NULL FIRSTNAME' THEN username
ELSE (firstname || ' ' || lastname)
END
AS full_name
FROM essay_table a
INNER JOIN essay\_writer\_join ej ON a.id = ej.essay_id
INNER JOIN writer_table u ON ej.user_id = u.id))
GROUP BY essay_id
CONNECT BY prev = PRIOR curr AND essay\_id = PRIOR essay\_id
START WITH curr = 1
The essays are unique but can have multiple writers (essay_writer_join