# Thread: doubt - it's urgent :(

1. Registered User
Join Date
Sep 2010
Posts
153

## Unanswered: doubt - it's urgent :(

I have a table named by products. Structure of the table is

products size
--------- ------
A 3.0
B 1/4-6.00
A 4/5-8.0
C 7.8
E 3.0-6/5
A 9.0
B 2.5-7.8

I want desired result like :-

products size1 size2 siZe3 size4 size5
--------- ----- ----- ------ ------ ------
A 3.0 4/5-8.0 9.0
B 2.5-7.8 1/4-6.00
C 7.8
E 3.0-6/5

Any help will be appreciated as it's very urgent

Thanks guys

2. Registered User
Join Date
Nov 2003
Posts
2,988
Originally Posted by sunny_007
as it's very urgent
Why is that?

3. Registered User
Join Date
May 2009
Posts
509
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?

Will you ONLY have up to 5 sizes?

4. Registered User
Join Date
Sep 2010
Posts
153

## Stealth DBA :)

Hi buddy,

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.

Thanks

5. Registered User
Join Date
May 2009
Posts
509
sunny 007, there are a few ways to do what you want. Here is one:
Code:
```WITH TEST_TAB (PRODUCTS, SIZE)
AS (
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'      )
SELECT PRODUCTS
, 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
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY PRODUCTS ORDER BY SIZE) AS SEQ
, PRODUCTS
, SIZE
FROM TEST_TAB
) 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.```

6. Registered User
Join Date
Sep 2010
Posts
153

## 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

Thanks

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•