Add Columns to Result that Don't Exist in Table?

  • I am trying to produce a query result that will be using a Case statement to determine values based on scores in a table for each row. The result needs to be exported to be used to upload to a state reporting website. My problem is that the state requires in the CSV file that is uploaded a lot of fields that we do not actually have in the database table we are creating the result set from. After I receive my result set using the Case statement, is there a way to add additional columns that don't actually exist in a table so I can export directly from SQL?

  • SELECT col1, col2, col3, CAST(NULL as BIT) as newCol4, CAST(NULL as VARCHAR(6)) as newCol5 FROM yourTable

    in this example, newCol4 and newCol5 are your non-existent columns. Using the CAST, ensures that they are the correct datatype, but you could also use either of these methods:

    SELECT col1, col2, col3, NULL as newCol4, '' as newCol5 FROM yourTable

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg (10/7/2015)


    SELECT col1, col2, col3, CAST(NULL as BIT) as newCol4, CAST(NULL as VARCHAR(6)) as newCol5 FROM yourTable

    in this example, newCol4 and newCol5 are your non-existent columns. Using the CAST, ensures that they are the correct datatype, but you could also use either of these methods:

    SELECT col1, col2, col3, NULL as newCol4, '' as newCol5 FROM yourTable

    I'm not exactly sure how the CSV file will honour CASTed columns.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil Parkin (10/7/2015)


    Jason Selburg (10/7/2015)


    SELECT col1, col2, col3, CAST(NULL as BIT) as newCol4, CAST(NULL as VARCHAR(6)) as newCol5 FROM yourTable

    in this example, newCol4 and newCol5 are your non-existent columns. Using the CAST, ensures that they are the correct datatype, but you could also use either of these methods:

    SELECT col1, col2, col3, NULL as newCol4, '' as newCol5 FROM yourTable

    I'm not exactly sure how the CSV file will honour CASTed columns.

    We can't be bothered with those pesky details.... 😀

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg (10/7/2015)


    SELECT col1, col2, col3, CAST(NULL as BIT) as newCol4, CAST(NULL as VARCHAR(6)) as newCol5 FROM yourTable

    in this example, newCol4 and newCol5 are your non-existent columns. Using the CAST, ensures that they are the correct datatype, but you could also use either of these methods:

    SELECT col1, col2, col3, NULL as newCol4, '' as newCol5 FROM yourTable

    I don't think I need to cast them because the data type itself doesn't need to be set, the column just has to be present in the CSV file or else the upload fails. Thank you for your help. I will give that a try.

  • What about specifying a specific value for a column in all rows?

  • bsmith 63193 (10/7/2015)


    What about specifying a specific value for a column in all rows?

    SELECT col1, col2, col3, 0 as newCol4, 'ABC' as newCol5 FROM yourTable

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply