Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Arvind Porlekar
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • 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.
 
Philip Grove
Ranch Hand
Posts: 68
Firefox Browser Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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
  • 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
Firefox Browser Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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
  • 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
Firefox Browser Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic