Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    102

    Unanswered: SELECT statement from a list of values

    Hi,

    I would like to write a select statement where I specify a list of values in the 'Select' line, and would like the output to have one line for each element.

    I tried using Case with no success. Any suggestions ?



    For example:

    Select a.id, a.timestamp, ('rowA','rowB') as 'Tag' from tableOne a where a.id = '1'


    So the 'where' line would produce one row, however, the overall statement would produce two.




    ID TimeStamp Tag
    --------------------------------
    1 2012-12-12 rowA
    1 2012-12-12 rowB

  2. #2
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75
    Just run the query twice:

    Select a.id, a.timestamp, ('rowA') as 'Tag' from tableOne a where a.id = '1'
    go
    Select a.id, a.timestamp, ('rowB') as 'Tag' from tableOne a where a.id = '1'

    I am not sure what problem you are trying to solve. Can you give us a detailed description of what it is that you are trying to do?

  3. #3
    Join Date
    Sep 2003
    Posts
    102
    Hi,

    I don't have a specific case that this is for, but just curious if it would be possible.

    The thought initially came out as I was trying to insert multiple rows into a table from a select statement; as in
    Insert into table2 (id, timedate, rowName)
    (select a.id, a.timedate, 'rowA' from table1 a where a.id = 1)


    The goal would be: to create a SINGLE select statement where one can specify the value of one of the columns.

    That sounds like what 'CASE' would do, but I just wasn't able to get it to work.
    Also, I feel doing a UNION or using a temp table would be cheating


    Thanks!

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Just an idea.
    Code:
    SELECT a.id
         , a.timestamp
         , b.Tag
     FROM  tableOne a
         ,(SELECT 'rowA' AS Tag UNION ALL SELECT 'rowB') b
     WHERE a.id = '1'

  5. #5
    Join Date
    Sep 2003
    Posts
    102
    Quote Originally Posted by tonkuma View Post
    Just an idea.
    Code:
    SELECT a.id
         , a.timestamp
         , b.Tag
     FROM  tableOne a
         ,(SELECT 'rowA' AS Tag UNION ALL SELECT 'rowB') b
     WHERE a.id = '1'
    That does work. But that is joining the values that a presented as a table.
    Which may be the only way to do it.

    Just wondering if there was a way to do it from just the 'SELECT" line.

    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
  •