Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: Query Column Name is Value in another table

    Hi
    i have Table1
    Columns Col1 ,col2
    Values AA A1
    BB A2
    CC B3


    Table 2
    Columns A1, A2, B3, B4 ...
    values x y z 1

    I want to get result :

    AA x
    BB y
    CC z

    Select Tab1.col1, (select Tab1.col2 from Table2 Tab2) from Table1 Tab1

    can Tab1.col2 evaluate to return value from Tab2?

    Thanks in Advance

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You could use the CASE expression for that:
    Code:
    select
      t1.col1,
      case t1.col2 
        when 'A1' then t2.a1
        when 'A2' then t2.a2
        ...
       end
    from table1 t1, table2 t2
    Make sure the columns in table2 are cast to the same type, if they are not originally of the same type.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2008
    Posts
    48
    Thanks Ivanov

    The case state works , but the problem is that my vertical table Table1 has 23 rows and it can grow .

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by phil72 View Post
    the problem is that my vertical table Table1 has 23 rows and it can grow .
    And why is that a problem?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2008
    Posts
    48
    Yes , using case it becomes hardcoded statement, when new rows are added to the table1, then i will have to change query again, as i dont know how many rows will be added and wat values these rows will have.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    But, you knows column names of table 2.
    So, you can include ELSE clause for the value of table 1 which doesn't match with column names of table 2.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by phil72 View Post
    then i will have to change query again, as i dont know how many rows will be added and wat values these rows will have.
    That's the price you pay for bad design.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Nov 2008
    Posts
    48
    Thanks Ivanov

    Any suggestions regarding my other thread

    can we use Merge and With Together

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You have to use Dynamic SQL, that it.

    Lenny

Posting Permissions

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