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

[Arvind Porlekar]

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 :

SELECT POLICYPARTY.USETYPE,POLICYPARTY.ROLECODE ,POLICYPARTY.POLICYID,pp.identity  FROM TBPOLICYPERIOD 
 pp,LIS.TBPOLICYPARTY AS POLICYPARTY
 where pp.policyId BETWEEN '500000000' AND '500000100'
 AND pp.POLICYPERIODTYPE='013' 
 AND pp.policyId = POLICYPARTY.POLICYID
AND POLICYPARTY.USEType ='004'

Output :

USETYPE	ROLECODE	POLICYID	   IDENTITY
004	             30	         500000000	A
004	             40	         500000000	A
004	             30	         500000001	A
004	             40	         500000001	A
004	             30	         500000002	A
004	             40	         500000002	A
004	             30	         500000003	A

i need output :

USETYPE	ROLECODE	POLICYID	   IDENTITY
004	             30,40	         500000000	A
004	             30,40	         500000001	A
004	             40,40	         500000002	A
004	             40,40	         500000003	A
004	             40,40	         500000004	A
004	             40,40	         500000005	A

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

Thanks
Arvind.

[Philip Grove]

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]

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]

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]

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]

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.