Rows to column


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)

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



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

 <tablename> A , 

 cntry_state B WHERE 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