Home » SQL & PL/SQL » SQL & PL/SQL » I think I need a pivot table. Not sure... (9i)
I think I need a pivot table. Not sure... [message #383848] Fri, 30 January 2009 12:58 Go to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
I'm using group by to get a count of distinct UWIs for a given barcode. The query works fine...

  select count(distinct(analytic.stripuwi(uwi))) counter, agat_samplepoint_id asi from ana_sample_details
  where trim(agat_samplepoint_id) is not null
  and agat_samplepoint_id <> '000000000'
  group by agat_samplepoint_id
  having count(distinct(analytic.stripuwi(uwi))) > 1
  order by count(distinct(analytic.stripuwi(uwi))) desc, agat_samplepoint_id desc


I want to add a third column to the resultset that is a delimited string of the UWIs that comprise the count. I suspect that some sort of pivot table is needed but I can't figure it out.

Thanks.
Re: I think I need a pivot table. Not sure... [message #383850 is a reply to message #383848] Fri, 30 January 2009 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Question

Regards
Michel
Re: I think I need a pivot table. Not sure... [message #383853 is a reply to message #383850] Fri, 30 January 2009 13:16 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
So the current resultset looks like this...

COUNTER     ASI
3           19723
3           12345
2           12187
2           16563


This means that ASI (barcode) 19723 is associated with three different locations (oil wells). That is a bad thing. Each barcode is supposed to be associated with one location. So this query finds all of the barcodes that have multiple locations associated with them. The problem is that it is not good enough for me to know which barcodes have multiple locations. I also want to know which locations are associated with these barcodes. Now, I could run a query to check each of these barcodes one at a time. But I would rather return a query that looks like this...

COUNTER     ASI     LOCATIONS
3           19723   loc1::loc2::loc3
3           12345   loc1::loc2::loc3
2           12187   loc1::loc2
2           16563   loc1::loc2
Re: I think I need a pivot table. Not sure... [message #383856 is a reply to message #383853] Fri, 30 January 2009 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for stragg, wm_concat...

Regards
Michel
Re: I think I need a pivot table. Not sure... [message #383869 is a reply to message #383856] Fri, 30 January 2009 16:38 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Stragg is a beautiful thing. The other concat function doesn't seem to exist in 9i. Thanks.
Re: I think I need a pivot table. Not sure... [message #383870 is a reply to message #383869] Fri, 30 January 2009 16:42 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
This is my final query...

select counter, asi, stragg(uwi) from (
  select count(distinct(analytic.stripuwi(uwi))) counter, agat_samplepoint_id asi from ana_sample_details
  where trim(agat_samplepoint_id) is not null
  and agat_samplepoint_id <> '000000000'
  group by agat_samplepoint_id
  having count(distinct(analytic.stripuwi(uwi))) > 1
  order by count(distinct(analytic.stripuwi(uwi))) desc, agat_samplepoint_id desc
  ) groupedValues, ana_sample_details asd
  where groupedValues.asi = asd.agat_samplepoint_id
  group by counter,asi

[Updated on: Fri, 30 January 2009 16:43]

Report message to a moderator

Re: I think I need a pivot table. Not sure... [message #383890 is a reply to message #383870] Sat, 31 January 2009 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback, wm_concat indeed does not exist in 9i.
I recommend you to use a tool to format your query in a "standard" way to make more readable and maintainable.
Have a look at SQL Formatter but they are many on web and in many development tools.

Regards
Michel

[Updated on: Sat, 31 January 2009 00:22]

Report message to a moderator

Re: I think I need a pivot table. Not sure... [message #383929 is a reply to message #383890] Sat, 31 January 2009 11:49 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
That's a fantastic tool. You are a wealth of information. Thanks.
Re: I think I need a pivot table. Not sure... [message #384130 is a reply to message #383890] Mon, 02 February 2009 04:37 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Not only does wm_concat not exist in 9i, but it isn't a documented feature in 10g - use with extreme caution.
Previous Topic: Loop (merged)
Next Topic: File operation(UTL_FILE)
Goto Forum:
  


Current Time: Sun Dec 04 02:43:27 CST 2016

Total time taken to generate the page: 0.20525 seconds