De-dupe

[userMikeD]

I'm trying to de-dupe and concatenate some values from a table to form a new "consolidated" view.

my data looks like this:
USER_ID APP CREATION_DATE APP_DATA
1 PF <timestamp> abc
1 PF <timestamp> abc
1 PF <timestamp> def
1 PF <timestamp> xyz
2 PF <timestamp> abc
2 PF <timestamp> def
3 PF <timestamp> abd

I want to be able to select this out:
USER_ID APP LATEST_DATE APP_DATA
1 PF <timestamp> abc,def,xyz
2 PF <timestamp> abc,def

Notice the duplicate record for user_id=1 and data=abc is only listed once, and also notice user_id=3 is not listed because there are no duplicates.

Here is my query. It's working except for removing the dupes.

SELECT user_id, app,
		 RTRIM(XMLELEMENT(root, XMLAGG(XMLELEMENT(node, app_data|| CHR(13)))).EXTRACT('//text()').getclobval(), CHR(13)) app_data,
		 MAX(creation_date) latest_date
  FROM user_action
 GROUP BY user_id, app
HAVING COUNT(1) > 1

So, I tried to use a smarter XPATH to select only unique values.

SELECT user_id, app_source,
		 RTRIM(XMLELEMENT(root, XMLAGG(XMLELEMENT(node, app_data || CHR(13)))).EXTRACT('//NODE[generate-id() = generate-id(key("distinct-node", .))]/text()').getclobval(), CHR(13)) app_data,
		 MAX(creation_date) latest_date
  FROM user_action
 GROUP BY user_id, app
HAVING COUNT(1) > 1

but Oracle's XML processor doesn't like the XPATH. Does anyone see a better way to do this? Any help is appreciated!

Thanks!

 

[userMikeD]

Hi,

I figured this out. I used a different XPATH to return unique nodes. So I have exactly what I need.

SELECT user_id, app_source,
		 RTRIM(XMLELEMENT(root, XMLAGG(XMLELEMENT(node, app_data || CHR(13)))).EXTRACT('//NODE[not(.=preceding::NODE)]/text()').getclobval(), CHR(13)) app_data,
		 MAX(creation_date) latest_date
  FROM user_action
 GROUP BY user_id, app
HAVING COUNT(1) > 1

Thanks!