All Forums Database
Mithafashi 10 posts Joined 11/11
08 Nov 2011
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

Amirtharaj.u 6 posts Joined 07/10
09 Nov 2011

 

 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

;

Amir

Mithafashi 10 posts Joined 11/11
09 Nov 2011

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

 

 

umakathir 32 posts Joined 09/06
14 Nov 2011

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 , 

 

 

You must sign in to leave a comment.