Win a copy of Beginning Java 17 Fundamentals: Object-Oriented Programming in Java 17 this week in the Java in General 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

How to get number of rows into Single row as comma separated.

 
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

I need help for getting number of rows into single row, Also i need those values should be comma separated.
i am using DB2. I am getting following data after i run the query.

Query :


Output :


i need output :




Can some one help me regarding this? I am using DB2.

Thanks
Arvind.
 
Ranch Hand
Posts: 68
Netbeans IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Check your output again because it's inconsistent. You might be looking for either of the string functions CONCAT or CONCAT_WS. You might need a GROUP BY clause on your statement to make it work.

Before you start wondering how to make this work I suggest that you start by fixing the other errors in your statement and enjoy the speed increases they give.
 
Arvind Porlekar
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Philip,

if you see the result set first....i am getting per policy id two role codes. so i need either function or modification in query such that the roles should come in single row with comma separated. I am using DB2 9.5 version.

Thanks
Arvind Porlekar
 
Philip Grove
Ranch Hand
Posts: 68
Netbeans IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You misunderstand what I mean by inconsistent. For IDENTITY=500000002 you have the ROLECODES 30 and 40 in the first table, and ROLECODE 40 twice in the table, that is potentially confusing.

The fact that you use DB2 9.5 might not be relevant as it is an SQL database and so far the attempts have been pure SQL.

By now I seriously doubt that adding a GROUP BY will actually solve anything, but try it anyway and post the result.
 
Arvind Porlekar
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Philip,

Here some confusion...here we have four column 1. USETYPE 2. ROLECODE 3. POLICYID 4.IDENTITY and in the result set i am getting per policy id two roles codes.

and i need per policy id, i need one row of role code with comma separated,


sorry for confusion.

Thanks and regards
Arvind Porlekar
 
Philip Grove
Ranch Hand
Posts: 68
Netbeans IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I advice caution when posting preformatted code, I think it's the formatting that complicated things.

Try a GROUP BY with a CONCAT and post the results.
 
WHAT is your favorite color? Blue, no yellow, ahhhhhhh! Tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic