Post reply

Concatenate 4 Columns with Dynamic SQL

  • June 20, 2012 at 6:48 am

    #271511

    Here is what I have:

    Col1= 1402, Col2 = 1502, Col3 = 1602, Col4 = 1702

    My End Goal is to have 1402 - 1502 - 1602 - 1702 as one column.

    Here is the SQL that I have:

    DECLARE @DynSQL VARCHAR(2000)

    SET @DynSQL = 'SELECT COL1' + ' + ' + 'COL2 FROM TableName'

    --PRINT (@DynSQL)

    EXEC (@DynSQL)

    The result of this is 1402 1502.

    When I try to replace the middle + sign (+ ' + ' +) with a hyphen like this + ' - ' +, I get the following error:

    Operand data type char is invalid for subtract operator.

    Right now I have two issues, I need to get the hyphen to work with concatenating two columns using Dynamic SQL and then I need to get all 4 Columns in there together with Hyphens.

    Some things to note:

    -This part of a much larger query that will have a parameter for a Database Name. We have 7 similar databases, all with a different name, and using Dynamic SQL will allow for one report instead of 7.

    -All the Columns I am trying to concatenate are of the CHAR datatype.

    Any and All help will be GREATLY appreciated.

    Thanks,

  • June 20, 2012 at 6:52 am

    #1503373

    You don't need dynamic SQL for this:

    SELECT NewColumn

    = CAST(Col1 AS VARCHAR(10)) + ' - '

    + CAST(Col2 AS VARCHAR(10)) + ' - '

    + CAST(Col3 AS VARCHAR(10)) + ' - '

    + CAST(Col4 AS VARCHAR(10))

    FROM MyTable

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 20, 2012 at 6:57 am

    #1503377

    I agree that what I presented does not need to be Dynamic SQL, BUT this is to be a part of much larger query where Dynamic SQL is needed. I tried to convey that in my "Things to Note" section below my problem.

    I believe that this concatenated column is whats causing my larger (Dynamic) query to fail. So I pulled it out and am trying to get it to work with Dynamic SQL, in hopes that I can then copy that back into the larger query and I can go on with my life.

    Thanks for the help though.

  • June 20, 2012 at 6:59 am

    #1503378

    SET @DynSQL = 'SELECT COL1 + '' - '' + COL2 FROM TableName'

    Quote problems....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 20, 2012 at 7:17 am

    #1503391

    Thank You Gail, this worked for concatenating two of the columns. I am now going to work on concatenating the 4 together.

    Thanks Again.

Viewing 5 posts - 1 through 4 (of 4 total)

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