Rows to column

Database

Rows to column

Input table should hown be converted into output as shown below.

Country          State

India               Ka

India               Kr

India               Dl

India               Hk

output should be

___________

Country          State

India               Ka,Kr,Dl,Hk

Tags (1)
1 REPLY

Re: Rows to column

  Hi ,   For your use....   Alter the below based on your requirement.

  WITH RECURSIVE cntry_state (country,state_nm,Fld_LEVEL) AS

 ( SELECT  country, CAST(state_nm  AS VARCHAR(200) ) , 0 AS Fld_LEVEL FROM

 <tablename> 

 UNION ALL

 SEL  B.country, B.state_nm||','||A.state_nm , Fld_LEVEL + 1  FROM 

 <tablename> A , 

 cntry_state B WHERE 

 A.country=B.country AND 

 POSITION ( A.state_nm IN B.state_nm)=0

 --  AND B.Fld_LEVEL < 6

 )

 SEL country , MAX(state_nm) FROM cntry_state  WHERE Fld_LEVEL 

= (SEL MAX(Fld_LEVEL) FROM cntry_state ) GROUP BY 1

;