aspose file tools*
The moose likes JDBC and the fly likes How to get number of rows into Single row as comma separated. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to get number of rows into Single row as comma separated." Watch "How to get number of rows into Single row as comma separated." New topic
Author

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

Arvind Porlekar
Ranch Hand

Joined: Dec 31, 2008
Posts: 38
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.
Philip Grove
Ranch Hand

Joined: Aug 18, 2009
Posts: 68

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

Joined: Dec 31, 2008
Posts: 38
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

Joined: Aug 18, 2009
Posts: 68

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

Joined: Dec 31, 2008
Posts: 38
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

Joined: Aug 18, 2009
Posts: 68

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to get number of rows into Single row as comma separated.