Post reply

T sql to reformat data from two columns to one nvarchar(max) field

  • July 23, 2012 at 5:38 am

    #264456

    Afternoon,

    I have a small reformating question that is stumping me. I would like to convert

    Column A ; Column B

    A ; The

    B ; iss

    C ; uout

    Into one variable nvarchar(max) field formated like:

    A

    The

    B

    iss

    C

    uout

    I think I need to use insert Carriage Return and New Line Feed in Code but I dont know how to merge the data like above. Any help would be greatfully recieved.

    Thanks,

    Oliver

  • July 23, 2012 at 5:49 am

    #1516160

    A simple concatination will do the trick adding in CHAR(10) + CHAR(13) for CR & LF

    declare @table table (ColA char(1),ColB char(4))

    insert into @table values ('A','The'),('B','iss'),('C','uout')

    declare @variable nvarchar(max) = ''

    select @variable = @variable + ColA + CHAR(10)+CHAR(13) + ColB + CHAR(10)+CHAR(13)+CHAR(10)+CHAR(13) FROM @table

    select @variable

  • July 23, 2012 at 5:55 am

    #1516163

    Thank you very much for the quick reply.

    Just a quick one, does the Line Feed not display in the SSMS results pane? I just get one long line at the moment.

    Many Thanks,

    Oliver

  • July 23, 2012 at 5:57 am

    #1516166

    Sorry worked it out, if I PRINT instead of SELECT it shows nicely.

    Many Thanks,

    Oliver

  • July 23, 2012 at 5:57 am

    #1516167

    No it doesnt as technically its 1 row of data so it displays it as 1 row.

    If you change the query output to text you will see it with the CR LF's.

  • July 25, 2012 at 12:35 am

    #1517178

    If you use SSMS of SQL Server 2012 the cariage return and line feed characters are copied when you use copy and paste.

  • July 25, 2012 at 1:57 am

    #1517200

    Thanks for that, something to look forward to when we upgrade.

  • July 25, 2012 at 4:58 pm

    #1517679

    oliver.morris (7/25/2012)

    Thanks for that, something to look forward to when we upgrade.

    Like Anthony suggested, just use the text mode instead of the grid mode. No sense in waiting.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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