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.