Post reply

ORDER BY is creating problem

  • August 9, 2010 at 6:04 am

    #224480

    Hi

    if you remove the "order by" then result will be right but if you use order by then concatenation will not occur

    declare @t table ( id int , num nvarchar(max))

    declare @STR nvarchar(max)

    set @STR = ''

    insert into @t

    select 1 , 'sssss' union

    select 13 , 'hgfffffffffff' union

    select 51 , 'gewgew' union

    select 61 , 'ddsdg' union

    select 71 , 'sa'

    select @STR = @STR + num from @t order by 1

    select @STR

    RESULT: sssss

    DESIRED OUTPUT : ssssshgfffffffffffgewgewddsdgsa

    please see it

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • August 9, 2010 at 6:20 am

    #1204103

    Bhuvnesh

    If you specify the column to order by by name instead of ordinal position then you get your concatenation.

    John

  • August 9, 2010 at 6:38 am

    #1204112

    thanks a lot

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • August 10, 2010 at 1:59 am

    #1204534

    removed

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • August 10, 2010 at 3:44 pm

    #1205069

    That concatenation trick is only supported for backward-compatibility reasons.

    It was never a good practice, and it remains slow and error-prone as you have discovered.

    There is also no guarantee that it will work as expected in future versions of SQL Server.

    A much better (faster and supported) alternative exists for string concatenation using FOR XML PATH:

    DECLARE @T

    TABLE (

    id INTEGER NOT NULL PRIMARY KEY,

    num NVARCHAR(MAX)

    );

    DECLARE @STR NVARCHAR(MAX);

    INSERT @T (id, num)

    SELECT 01, N'sssss' UNION ALL

    SELECT 13, N'hgfffffffffff' UNION ALL

    SELECT 51, N'gewgew' UNION ALL

    SELECT 61, N'ddsdg' UNION ALL

    SELECT 71, N'sa&sa';

    SET @STR =

    (

    SELECT T.num AS [text()]

    FROM @T T

    ORDER BY T.id

    FOR XML

    PATH (''),

    TYPE

    ).value('./text()[1]', 'NVARCHAR(MAX)');

    SELECT @STR AS concatenated;

    I took the liberty of tidying up your example code too 🙂

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • August 10, 2010 at 9:42 pm

    #1205121

    Thanks Paul

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

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