August 20, 2012 at 3:30 am
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
August 20, 2012 at 4:25 am
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
August 20, 2012 at 4:33 am
durai nagarajan (8/20/2012)
if you want the result like thisTranIDCode
201205AMDDOH
201206IXJRMI
then the query should be
select TranID , OriginCode + DestinationCode 'Code' from #Testif 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
August 20, 2012 at 4:41 am
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
August 20, 2012 at 4:56 am
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
August 20, 2012 at 4:57 am
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:
August 20, 2012 at 5:08 am
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
August 20, 2012 at 5:12 am
Brilliant query guys , i am poor .. good learning
Regards
Durai Nagarajan
August 20, 2012 at 5:40 am
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
August 20, 2012 at 7:22 am
This thread can help you.
http://www.sqlservercentral.com/Forums/Topic1335869-391-1.aspx
August 20, 2012 at 8:29 am
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
August 20, 2012 at 8:33 am
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:
August 20, 2012 at 8:53 am
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;
August 20, 2012 at 10:03 am
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
August 21, 2012 at 4:49 am
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