Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Question Unanswered: Combine multiple rows into one

    I have two tables that I need to join to get the required data, but I get too many rows returned with everything I have tried. Basically, both tables have and IDEA_ID field, and a simple SELECT query on TABLE1 will give me the data that I want from that table, but I get too many rows returned from TABLE2 as there are multiple rows with the same IDEA_ID in TABLE2.

    Basically,

    SELECT *
    FROM TABLE1

    gives me all the data I need with the exception of 6 columns for which the data is in TABLE2.

    TABLE2 has 3 columns - IDEA_ID, ROLE_ID and ADATE. IDEA_ID matches the IDEA_ID field in TABLE1; ROLE_ID is a number from 1 to 6; and ADATE is a date. There can be 1 to 6 rows for each IDEA_ID, with the IDEA_ID and ROLE_ID combination being unique.

    How can I run a query that will give me rows that have the fields from TABLE1, and 6 additional columns from TABLE2?

    Table structures are:

    TABLE1
    IDEA_ID, PROD_TYPE, TITLE, DESC

    TABLE2
    IDEA_ID, ROLE_ID, ADATE
    Make something idiot proof and someone will make a better idiot...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Which six columns do you need from the second table?

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    Pat

    For each IDEA_ID in TABLE2, there are potentially 6 rows with that IDEA_ID, but with a number from 1 to 6 in the ROLE_ID column.

    What I'm trying to do is to create a dataset that has all of the columns from TABLE1, but I want to create 6 new columns with data from TABLE2. Each of the new columns will represent a row from TABLE2 for each of the values of ROLE_ID from 1 to 6. The value that will go into each of these columns is the value in ADATE.

    I can query TABLE2 to get these values:

    SELECT idea_id,
    decode(role_id, 1, adate),
    decode(role_id, 2, adate),
    decode(role_id, 3, adate),
    decode(role_id, 4, adate),
    decode(role_id, 5, adate),
    decode(role_id, 6, adate)
    FROM TABLE2

    but this produces 6 rows of output. What I want from TABLE2 is a single row with each of these values. The query above produces a dataset with IDEA_ID plus 6 columns, each row having only one of those 6 column containing a value.

    How do I produce just one row of output with all 6 of these columns having a value in it?
    Make something idiot proof and someone will make a better idiot...

Posting Permissions

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