String concatenation

  • Hi

    I have the following table

    CREATE TABLE #Test

    (ID int NOT NULL IDENTITY(1,1) Primary key,

    TranID int NOT NULL,

    OriginCode varchar(5) NOT NULL,

    DestinationCode varchar(5) NOT NULL

    )

    GO

    INSERT INTO #Test

    SELECT 201205 ,'AMD','DOH'

    UNION ALL

    SELECT 201205 ,'DOH','NBO'

    UNION ALL

    SELECT 201205 ,'NBO','ADD'

    UNION ALL

    SELECT 201205 ,'ADD','DEL'

    UNION ALL

    SELECT 201205 ,'DEL','AMD'

    UNION ALL

    SELECT 201206 ,'IXJ','RMI'

    GO

    SELECT * FROM #Test

    DROP TABLE #Test

    i want the following result

    TranIDCode

    201205AMDDOHNBOADDDELAMD

    201206IXJRMI

    Thanks

  • if you want the result like this

    TranIDCode

    201205AMDDOH

    201206IXJRMI

    then the query should be

    select TranID , OriginCode + DestinationCode 'Code' from #Test

    if this is not as per your output post a detailed explaination.

    Regards
    Durai Nagarajan

  • durai nagarajan (8/20/2012)


    if you want the result like this

    TranIDCode

    201205AMDDOH

    201206IXJRMI

    then the query should be

    select TranID , OriginCode + DestinationCode 'Code' from #Test

    if this is not as per your output post a detailed explaination.

    No that won't give the concatenation that the OP is after, we are looking for a distinct row for TranID with the concatenation of Code and Destination in ID (hierarchical) order

    201205AMDDOHNBOADDDELAMD

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • it is my miss , how about this.

    set nocount on

    CREATE TABLE #Test

    (ID int NOT NULL IDENTITY(1,1) Primary key,

    TranID int NOT NULL,

    OriginCode varchar(5) NOT NULL,

    DestinationCode varchar(5) NOT NULL

    )

    GO

    CREATE TABLE #Test2

    (TranID int NOT NULL,

    Code varchar(100) NOT NULL,

    )

    go

    INSERT INTO #Test

    SELECT 201205 ,'AMD','DOH'

    UNION ALL

    SELECT 201205 ,'DOH','NBO'

    UNION ALL

    SELECT 201205 ,'NBO','ADD'

    UNION ALL

    SELECT 201205 ,'ADD','DEL'

    UNION ALL

    SELECT 201205 ,'DEL','AMD'

    UNION ALL

    SELECT 201206 ,'IXJ','RMI'

    GO

    DECLARE @Tran_id int, @Tran_name nvarchar(50)

    DECLARE test_cursor CURSOR FOR

    SELECT TranID, OriginCode + DestinationCode FROM #Test

    OPEN test_cursor

    FETCH NEXT FROM test_cursor

    INTO @Tran_id, @Tran_name

    WHILE @@FETCH_STATUS = 0

    BEGIN

    if exists ( select 'x' from #Test2 where TranID = @Tran_id)

    Begin

    update #Test2

    set code = code+ @Tran_name

    where TranID = @Tran_id

    end

    else

    begin

    insert into #Test2 select @Tran_id, @Tran_name

    end

    FETCH NEXT FROM test_cursor

    INTO @Tran_id, @Tran_name

    End

    CLOSE test_cursor

    DEALLOCATE test_cursor

    select * from #Test2

    DROP TABLE #Test

    DROP TABLE #Test2

    set nocount off

    Regards
    Durai Nagarajan

  • something like this maybe?

    SELECT

    tranid,

    (

    SELECT OriginCode + DestinationCode

    FROM #test p2

    WHERE p1.tranid = p2.tranid

    FOR XML PATH('')

    )

    FROM #test p1

    GROUP BY tranid

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • You beat me to it by minutes!

    SELECTT1.TranID,

    (

    SELECTOriginCode + DestinationCode

    FROM#Test T2

    WHERET2.TranID = T1.TranID

    ORDER BY

    OriginCode

    FOR XML PATH('') ) TheString

    FROM#Test T1

    GROUP BY

    TranID ;

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • thanks for the reply

    but i didn't get the expected result

    TranIDCode

    201205AMDDOHNBOADDDELAMD

    201206IXJRMI

    I don't want to use Cursor as i am using this query as subquery in other query

  • Brilliant query guys , i am poor .. good learning

    Regards
    Durai Nagarajan

  • Really ugly recursive CTE solution (I'm hoping someone comes up with a better solution). The performance for this will scale terribly.

    WITH CTE AS (

    SELECT ID, TranID,

    CASE WHEN a.N = 1 THEN OriginCode ELSE DestinationCode END AS code,

    ROW_NUMBER() OVER(PARTITION BY TranID ORDER BY ID, a.N) AS pos

    FROM #Test

    CROSS JOIN (SELECT 1 UNION ALL SELECT 2)a(N)),

    CTE2 AS (

    SELECT ID, TranID, code, CAST(code AS VARCHAR(MAX)) AS overallCode, pos

    FROM CTE

    WHERE pos = 1

    UNION ALL

    SELECT b.ID, b.TranID, b.code, overallCode + b.code, b.pos

    FROM CTE2 a

    INNER JOIN CTE b ON a.TranID = b.TranID AND a.pos < b.pos AND a.code <> b.code)

    SELECT TranID, overallCode

    FROM (SELECT TranID, overallCode, ROW_NUMBER() OVER(PARTITION BY TranID ORDER BY LEN(overallCode) DESC)

    FROM CTE2) a(TranID, overallCode, ItemNumber)

    WHERE ItemNumber = 1;

    Results: -

    TranID overallCode

    ----------- --------------------

    201205 AMDDOHNBOADDDELAMD

    201206 IXJRMI


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • This thread can help you.

    http://www.sqlservercentral.com/Forums/Topic1335869-391-1.aspx

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • does this work for you?

    ;with cte as

    (

    SELECT ID, TranID, OriginCode AS scode, 1 as col

    FROM #Test

    union all

    SELECT ID, tranID, DestinationCode AS scode, 2 as col

    FROM #Test

    ),

    cte2 as

    (

    select DISTINCT DENSE_RANK() over (partition by tranid order by (Id+col)) dr, tranid, scode from cte

    )

    SELECT

    tranid,

    (

    SELECT sCode + ''

    FROM cte2 p2

    WHERE p1.tranid = p2.tranid

    FOR XML PATH('')

    )

    FROM cte2 p1

    GROUP BY tranid

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Great piece of SQL J Livingston - you've beat me to it again... I was working on a CTE solution...

    Next time....

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • J Livingston SQL (8/20/2012)


    does this work for you?

    ;with cte as

    (

    SELECT ID, TranID, OriginCode AS scode, 1 as col

    FROM #Test

    union all

    SELECT ID, tranID, DestinationCode AS scode, 2 as col

    FROM #Test

    ),

    cte2 as

    (

    select DISTINCT DENSE_RANK() over (partition by tranid order by (Id+col)) dr, tranid, scode from cte

    )

    SELECT

    tranid,

    (

    SELECT sCode + ''

    FROM cte2 p2

    WHERE p1.tranid = p2.tranid

    FOR XML PATH('')

    )

    FROM cte2 p1

    GROUP BY tranid

    Excellent! I knew the ugly recursive CTE I wrote wasn't necessary.

    I think that this improves your code slightly: -

    WITH CTE AS (SELECT DISTINCT TranID,

    CASE WHEN a.N = 1 THEN OriginCode ELSE DestinationCode END AS code,

    DENSE_RANK() OVER (PARTITION BY TranID ORDER BY ID+a.N) AS pos

    FROM #Test

    CROSS JOIN (SELECT 1 UNION ALL SELECT 2)a(N)

    )

    SELECT TranID,(SELECT code + ''

    FROM CTE b

    WHERE a.TranID = b.TranID

    ORDER BY b.pos

    FOR XML PATH('')) AS overallCode

    FROM CTE a

    GROUP BY TranID;


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Cadavre (8/20/2012)


    I think that this improves your code slightly: -

    WITH CTE AS (SELECT DISTINCT TranID,

    CASE WHEN a.N = 1 THEN OriginCode ELSE DestinationCode END AS code,

    DENSE_RANK() OVER (PARTITION BY TranID ORDER BY ID+a.N) AS pos

    FROM #Test

    CROSS JOIN (SELECT 1 UNION ALL SELECT 2)a(N)

    )

    SELECT TranID,(SELECT code + ''

    FROM CTE b

    WHERE a.TranID = b.TranID

    ORDER BY b.pos

    FOR XML PATH('')) AS overallCode

    FROM CTE a

    GROUP BY TranID;

    nicely done...wish I could think that way !!

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Thanks J Livingston and Cadavre for the solution

Viewing 15 posts - 1 through 15 (of 23 total)

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