Need help in Query : Recursive query :


Need help in Query : Recursive query :

Hi All,

I have got a scenario where I am supposed to obtain a size profile for a particular product in a factory. Here goes my input table.

Input : ProductSizeRto

Product Factory Size Ratio Last Record Ind Rank
123 1 M 10 N 1
123 1 XL 30 N 2
123 1 S 60 Y 3
456 2 L 20 N 1
456 2 XXL 35 N 2
456 2 M 45 Y 3

The rank and last record  is based on the Ratio in Asc and Partioned by Product and Factory. And a product and factory combination can have "N" number of sizes. Hence, the query has to be a dynamic one. Now, I want to see the size profile of each product and factory based on the ratio.

Rule is : Size Profile : <Size of rank1>||<Ratio of rank 1>||<Size of rank2>||<Ratio of Rank2>||....||<Size of rank N>||<Ratio of rank N>

PFB the sample output of the same :

Output : SzPrfl 

Product Factory Profile Last Record Ind
123 1 M10XL30S60 Y
456 2 L20XXL35M45 Y

I could generate this scenario using stored proc and loop by assigning variable. Is there a way I can do using SQL query only ( recursive query ? ). As I'm not familier with recursive query much, seeking help from the geeks.

Your response is much appreciated. :)