Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: get multiple records for one record

    Hi,

    I have a requirement where i need to produce multiple records for a single record.

    Example:
    Input:
    col1 col2 col3
    1 A &
    2 B $
    3 C #
    4 D @
    Output:
    col1 col2 col3 col4
    1 A & 1
    1 B $ 2
    2 C # 1
    2 D @ 2

    That means for each value of "column1" i want to get next two records with "column2", "column3" and "column4" which gives the count.

    any help would be greatly appreciated.

    Thanks,
    Sri

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sri.P
    i need to produce multiple records for a single record.
    Why aren't you producing multiple records for those where col1 is 3 and 4?


    Quote Originally Posted by sri.P
    i want to get next two records
    Next based on what? Records in the database table do not have any inherent order, so "next two records" essentially means "any two random records".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That's an FAQ: Search for "pivot" tables.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't understand fully your requirements,
    because your example was too small and I thought that your description was contradicted with your example.

    Your output rows in the example seems to be corresponded one by one with your input rows.

    Anyhow, your output would be produced like this:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     input(col1, col2, col3) AS (
    VALUES
     (1, 'A', '&')
    ,(2, 'B', '$')
    ,(3, 'C', '#')
    ,(4, 'D', '@')
    )
    SELECT (col1 + 1) / 2 AS col1
         , col2
         , col3
         , MOD(col1 - 1, 2) + 1 AS col4
      FROM input
    ;
    ------------------------------------------------------------------------------
    
    COL1        COL2 COL3 COL4       
    ----------- ---- ---- -----------
              1 A    &              1
              1 B    $              2
              2 C    #              1
              2 D    @              2
    
      4 record(s) selected.
    If col1 were not consecutive, ROW_NUMBER OLAP function would be a solution:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     input(col1, col2, col3) AS (
    VALUES
     (1, 'A', '&')
    ,(2, 'B', '$')
    ,(3, 'C', '#')
    ,(4, 'D', '@')
    ,(6, 'E', '%')
    ,(8, 'F', '*')
    ,(9, 'G', '/')
    )
    SELECT (col1_seq + 1) / 2 AS col1
         , col2
         , col3
         , MOD(col1_seq - 1, 2) + 1 AS col4
      FROM (SELECT ip.*
                 , INTEGER( ROW_NUMBER() OVER(ORDER BY col1) ) AS col1_seq
              FROM input AS ip
           ) AS s
    ;
    ------------------------------------------------------------------------------
    
    COL1        COL2 COL3 COL4       
    ----------- ---- ---- -----------
              1 A    &              1
              1 B    $              2
              2 C    #              1
              2 D    @              2
              3 E    %              1
              3 F    *              2
              4 G    /              1
    
      7 record(s) selected.
    Last edited by tonkuma; 07-03-09 at 21:49.

Posting Permissions

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