SQL Query + default fields in the output

I want to select some rows from a table.

Along with the normal columns that I get back from the query result, I also need to append some additional field in the result set.

I am exporting the table in to the csv file. The output file will have some extra fields along with the normal column values that were returned from the query.

For ex table has column A, B, and C. which has A B C
11 Bob S
12 Gary J
13 Andy K

Now my output file should have

11, Bob , S, "DDD" , 1
12, Gary, J, "DDD" , 2
13, Andy, K , "DDD" , 3

(One way was to first do select A,B,C from test_table and then manipulate the file by appending the 2 fields manually)

Is there any way that I can get the extra 2 values in the query itself as hardcoded values ?

From stackoverflow

• SELECT A, B, C, 'DDD' as "D", 1 as "E"
  FROM myTable
  WHERE ...
  

  Is the 1, 2, 3 hardcoded, or does this need to be incremented value?

  Jitesh Dani : 1,2,3 needs to be incremental

• Yes, in fact this is pretty easy. It would be something like this: SELECT field1, field2, 'Default' AS field3, 'Default2' AS field4 FROM table WHERE ...

• How do I get the incremental value in the last field?

  Jens Schauder : for additional comments and clarification you should edit the original question

  Simon Hughes : For MS SQL Server you can use the ROW_NUMBER, or alternatively you should look here http://stackoverflow.com/questions/510769/sql-query-that-numerates-the-returned-result

• Use can use a sub query to get the incremental value. This relies upon an order by and unique values in column A.

  SELECT
      A,
      B,
      C,
      'DDD' [D],
      (SELECT COUNT(*) FROM test_table test_table2 WHERE test_table2.A <= test_table.A) [E]
  FROM
      test_table
  ORDER BY
      A
  

  This is blindly answering your question... I have a feeling there is more to your question and much better ways of actually achieving your final goal.

• You can get the incrementing field with oracle with the pseudo column rownum or row_num

  I keep forgetting. But this will differ for other databases.

• SELECT  A,
          B,
          C,
          'DDD' AS D,
          ROW_NUMBER() OVER (ORDER BY A) AS 'E'
  FROM    myTable
  

  Also look here for an answer sql-query-that-numerates-the-returned-result

• If using SQL Server 2005+, using the ROWNUMBER function should do fine.

  SELECT A, B, C, 'DDD' AS D,
  ROW_NUMBER() OVER (ORDER BY A) AS E
  FROM Table
  

• I'm gussing the last field is a row counter and you don't specify the database, but for Oracle this should work:

  select A, B, C, 'DDD', rownum from ex;