SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


String concatenation


String concatenation

Author
Message
SQL006
SQL006
Old Hand
Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)

Group: General Forum Members
Points: 376 Visits: 1312
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

TranID Code
201205 AMDDOHNBOADDDELAMD
201206 IXJRMI




Thanks
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1621 Visits: 2775
if you want the result like this

TranID Code
201205 AMDDOH
201206 IXJRMI

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
Andy Hyslop
Andy Hyslop
SSC Eights!
SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)

Group: General Forum Members
Points: 985 Visits: 3037
durai nagarajan (8/20/2012)
if you want the result like this

TranID Code
201205 AMDDOH
201206 IXJRMI

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

201205 AMDDOHNBOADDDELAMD


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1621 Visits: 2775
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
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5511 Visits: 35456
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

Robin Sasson
Robin Sasson
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 Visits: 530
You beat me to it by minutes!


SELECT T1.TranID,
(
SELECT OriginCode + DestinationCode
FROM #Test T2
WHERE T2.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
SQL006
SQL006
Old Hand
Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)

Group: General Forum Members
Points: 376 Visits: 1312
thanks for the reply

but i didn't get the expected result

TranID Code
201205 AMDDOHNBOADDDELAMD
201206 IXJRMI

I don't want to use Cursor as i am using this query as subquery in other query
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1621 Visits: 2775
Brilliant query guys , i am poor .. good learning

Regards
Durai Nagarajan
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3930 Visits: 8472
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

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16768 Visits: 19121
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search