This week's book giveaway is in the Other Languages forum.
We're giving away four copies of Rust Web Development and have Bastian Gruber on-line!
See this thread for details.
Win a copy of Rust Web Development this week in the Other Languages forum!
  • 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:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

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: 40913
840
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: 40913
840
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic