Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Smile 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. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by sunny_007 View Post
    as it's very urgent
    Why is that?

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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. #4
    Join Date
    Sep 2010
    Posts
    153

    Smile 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. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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. #6
    Join Date
    Sep 2010
    Posts
    153

    Smile 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
  •