• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

create comma separated value as output from a query.

 
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi In table 'TableTest' empid exists in two rows

EMPID EMPNAME EMPDEPT EMPPOBOx

111 xyz 123 14231

222 pqr 456 9876

111 xyz 987 14231



I want the output as

empid empdept

111 123,987



Can anyone suggest me how do i get comma separated value as output.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Where is your report going? If JDBC, this is easier to do in Java. If it is direct Oracle, I think you are going to need to write a stored procedure.
 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The report is going back into java servlet(JDBC) but i want to get the output from my oracle query .... i will write a stored procedure to get output .. but was expecting some function that gives the comma separated value based on some condition.
 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If possible do it in java.

Maybe this can help,

SELECT EMPID,
MAX(dept1) || ',' || MAX(dept2) || ',' || MAX(dept3) || ',' || MAX(dept4)
FROM
(SELECT EMPID,
CASE ranking
WHEN 1 THEN
EMPDEPT
ELSE
NULL
END dept1,
CASE ranking
WHEN 2 THEN
EMPDEPT
ELSE
NULL
END dept2,
CASE ranking
WHEN 3 THEN
EMPDEPT
ELSE
NULL
END dept3,
CASE ranking
WHEN 4 THEN
EMPDEPT
ELSE
NULL
END dept4
FROM
(SELECT rank() over(
ORDER BY EMPDEPT) AS
ranking,
EMPDEPT,
EMPID
FROM test
WHERE EMPID = '111')
)
GROUP BY EMPID

But it has few drawbacks :
Not sure rank() is ANSI SQL standard. If not it will not be supported in non-oracle databases.
This will work only if you know the maximum number of departments an employee can work in.
Query is may require some fine tuning.
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

ruquia tabassum wrote:The report is going back into java servlet(JDBC)


In my opinion, it's easier and clearer to do in Java.
 
reply
    Bookmark Topic Watch Topic
  • New Topic