Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

String concatenation Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 3:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 26, 2014 1:59 AM
Points: 181, Visits: 987
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
Post #1347038
Posted Monday, August 20, 2012 4:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1347064
Posted Monday, August 20, 2012 4:33 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 689, Visits: 2,767
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
Post #1347068
Posted Monday, August 20, 2012 4:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1347070
Posted Monday, August 20, 2012 4:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 1,887, Visits: 18,572
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 !
__________________________________________________________________
Post #1347075
Posted Monday, August 20, 2012 4:57 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 27, 2014 7:41 AM
Points: 739, Visits: 518
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 ;




"Be brave. Take risks. Nothing can substitute experience."
Post #1347076
Posted Monday, August 20, 2012 5:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 26, 2014 1:59 AM
Points: 181, Visits: 987
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
Post #1347083
Posted Monday, August 20, 2012 5:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
Brilliant query guys , i am poor .. good learning

Regards
Durai Nagarajan
Post #1347085
Posted Monday, August 20, 2012 5:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 2,422, Visits: 7,444
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



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1347097
Posted Monday, August 20, 2012 7:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 3,374, Visits: 7,302
This thread can help you.

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



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1347157
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse