Rows to column | SQL looping | reccursion

Database
Enthusiast

Rows to column | SQL looping | reccursion

Have to create a view from base table below. The record count of base table is not constant.

Base table:-

-----------------------

Country | State

----------------------

India      KL

India      TN

India      KA

India      WB

India      UP

India      MP

View:-

----------------------

Country  | State 

----------------------

India         KL-TN-KA-WB-UP-MP

Tags (1)
3 REPLIES
Enthusiast

Re: Rows to column | SQL looping | reccursion

 Hi,

        It's a sample query to provide you some idea on the recursive SQLs to satisfy your requirement. You  can modify it based on your actual requirement and performance stats.

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

;

Enthusiast

Re: Rows to column | SQL looping | reccursion

Thanks Amir.

This is working, but Fld_LEVEL is not available in the base table and cannot be created throught a derived table or sub query. Both of them are not supported by RECURSIVE query.

Have created one more view on base table with Fld_LEVEL. Then the select query is working fine

When it is converted into a recursive view not able to accomodate below part

WHERE Fld_LEVEL 

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

Any Ideas?

Thanks,

Mithafashi

Enthusiast

Re: Rows to column | SQL looping | reccursion

Fld_LEVEL is the temp column to track the depth of the process. In the seed query, it has been intialized with 0

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

 <tablename> 

 UNION ALL

and the same value is being incremented in the recursive query

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

 <tablename> A ,