Is there any order to the Size values per Product? Any Ordering you do will be based on left justified Alpha sorting. For A 3 comes before 4 and 4 comes before 9 (ascending order) but for B 2 comes AFTER 1 (descending order). You can't do both ascending and descending on the same column at the same time.
If it is not based on an Alpha sort, what is the order based on? Is it random?
It doesnt matter how the order is, ascendign or descending. All i want is the values. However it comes, I just need to put the values into another table in an order, i mean all the values belonging to a particular product ion a row.
sunny 007, there are a few ways to do what you want. Here is one:
WITH TEST_TAB (PRODUCTS, SIZE)
SELECT 'A', '3.0' UNION ALL
SELECT 'B', '1/4-6.00' UNION ALL
SELECT 'A', '4/5-8.0' UNION ALL
SELECT 'C', '7.8' UNION ALL
SELECT 'E', '3.0-6/5' UNION ALL
SELECT 'A', '9.0' UNION ALL
SELECT 'B', '2.5-7.8' )
, MAX(CASE SEQ WHEN 1 THEN SIZE END) AS SIZE1
, MAX(CASE SEQ WHEN 2 THEN SIZE END) AS SIZE2
, MAX(CASE SEQ WHEN 3 THEN SIZE END) AS SIZE3
, MAX(CASE SEQ WHEN 4 THEN SIZE END) AS SIZE4
, MAX(CASE SEQ WHEN 5 THEN SIZE END) AS SIZE5
SELECT ROW_NUMBER() OVER(PARTITION BY PRODUCTS ORDER BY SIZE) AS SEQ
) AS A
GROUP BY PRODUCTS
PRODUCTS SIZE1 SIZE2 SIZE3 SIZE4 SIZE5
-------- -------- -------- -------- -------- --------
A 3.0 4/5-8.0 9.0 - -
B 1/4-6.00 2.5-7.8 - - -
C 7.8 - - - -
E 3.0-6/5 - - - -
4 record(s) selected.
Stealth DBA :) hehehe wow, you are superb :) Thanks a lot:)
Heheheheh wow buddy you are superb. Thanks a lot for the solution.
One more doubt i have...
Suppose, it's a transaction table. data are not known to us. Comtinuosly, data are coming (products and their size). We don't know how many sizes for a particular product. Transaction table it is then how do we solve this problem?
I will be really really thankful to you if you provide a solution to it Kindly try buddy, please