January 23, 2010 at 11:01 am
My table and row as follow,
declare @tCounter table
(
TrnxID [int] IDENTITY(1,1) NOT NULL,
CoutCd varchar(10) not null,
CoutDesc varchar(50) not null
)
--TrnxID is a primary key
--CoutCd is a unique
insert into @tCounter(CoutCd,CoutDesc) values('HPT','Hentian Putra');
insert into @tCounter(CoutCd,CoutDesc) values('KT','Kuala Terengganu');
insert into @tCounter(CoutCd,CoutDesc) values('Kntn','Kuantan');
insert into @tCounter(CoutCd,CoutDesc) values('JB','Johor Bharu');
declare @tRouteH table
(
TrnxID [int] IDENTITY(1,1) NOT NULL,
RouteCd varchar(10) not null,
Mileage numeric(5,2) not null
)
--TrnxID is a primary key
--RouteCd is a unique
insert into @tRouteH(RouteCd,Mileage) values('_R00000001',497.23);
insert into @tRouteH(RouteCd,Mileage) values('_R00000002',250.45);
declare @tRouteD table
(
TrnxID int identity(1,1) not null,
RouteCd varchar(10) not null,
CoutCd varchar(10) not null,
Sequence tinyint not null
)
--TrnxID is a primary key
--RouteCd is a Foreign Key refer to @tRouteH
--CoutCd is a Foreign Key refer to @tCounter
--RouteCd and CoutCd is a unique
--CoutCd is a unique
insert into @tRouteD(RouteCd,CoutCd,Sequence) values('_R00000001','KT',1);
insert into @tRouteD(RouteCd,CoutCd,Sequence) values('_R00000001','Kntn',2);
insert into @tRouteD(RouteCd,CoutCd,Sequence) values('_R00000001','JB',3);
insert into @tRouteD(RouteCd,CoutCd,Sequence) values('_R00000002','HPT',1);
insert into @tRouteD(RouteCd,CoutCd,Sequence) values('_R00000002','JB',2);
How to built SQL to display as follow,
RouteCd | AllCoutCd | AllCoutDesc
-------------------------------------------------------
_R00000001 | KT - Kntn - JB | Kuala Terengganu - Kuantan - Johor Bharu
_R00000002 | HPT - JB | Hentian Putra - Johor Bharu
AllCoutCd is order by Sequence
Me looking for help 🙁
January 23, 2010 at 12:11 pm
Just replaced table variable with temp tables
;WITH CTE AS
(SELECT R.TrnxID, RoutecD, R.CoutCD, Sequence, CoutDesc FROM
#TRouteD R
JOIN #tCounter S ON R.CoutCD = S.CoutCD)
SELECT RouteCD,
STUFF((SELECT '-'+p.CoutCD
FROM CTE p
WHERE p.RouteCD = R.RouteCD
--ORDER BY Sequence
FOR XML PATH('')),1,1,''),
STUFF((SELECT '-'+p.CoutDesc
FROM CTE p
WHERE p.RouteCD = R.RouteCD
--ORDER BY Sequence
FOR XML PATH('')),1,1,'')
FROM CTE R
GRoup BY RouteCD
---------------------------------------------------------------------------------
January 23, 2010 at 12:12 pm
I would use a CTE to join the two tables and use the FOR XML clause to get the results.
;WITH cte AS
(
SELECT RouteCd,rd.CoutCd,CoutDesc,SEQUENCE
FROM @tRouteD rd
INNER JOIN @tCounter c ON rD.CoutCd=c.CoutCd
)
SELECT
RouteCd,
STUFF((SELECT ' - ' + CoutCd FROM cte c2 WHERE c1.RouteCd=c2.RouteCd ORDER BY SEQUENCE FOR XML PATH('')),1,3,'') AS AllCoutCd,
STUFF((SELECT ' - ' + CoutDesc FROM cte c2 WHERE c1.RouteCd=c2.RouteCd ORDER BY SEQUENCE FOR XML PATH('')),1,3,'') AS AllCoutDesc
FROM cte c1
GROUP BY RouteCd
/* result set
RouteCdAllCoutCdAllCoutDesc
_R00000001KT - Kntn - JBKuala Terengganu - Kuantan - Johor Bharu
_R00000002HPT - JBHentian Putra - Johor Bharu
*/
January 23, 2010 at 12:16 pm
Did you notice that you've got two answers with sample code after that short time?
The reason is simple: You provided ready to use sample data! Well done! 🙂
Having two almost identical solutions seems to be an indicator that either both of us are hitting in the wrong direction or the concept of the solution isn't that bad... 😉
January 23, 2010 at 12:24 pm
lmu92 (1/23/2010)
Did you notice that you've got two answers with sample code after that short time?The reason is simple: You provided ready to use sample data! Well done! 🙂
Cent percent for the OP for putting together the data in a consumable format and suprisingly this was her first visit here on SSC it seems ...
Having two almost identical solutions seems to be an indicator that either both of us are hitting in the wrong direction or the concept of the solution isn't that bad... 😉
Or too much of CTEs in our minds these days..:-D I have seen lot of your solutions making use of CTE's lutz 🙂
---------------------------------------------------------------------------------
January 23, 2010 at 12:49 pm
Nabha (1/23/2010)
I have seen lot of your solutions making use of CTE's lutz 🙂
I can't really do anything about it: as soon as I think a subquery is required for the solution I almost immediately use CTEs.
I guess I'm not even able to write the "old-fashioned" subquery anymore...
The "bad" thing about it: since CTEs made it so easy to use subqueries I think I tend to not spending enough time on trying to find an easier solution for the problem... :ermm:
And I have to admit that I don't study the execution plan of each and every solution I post to verify if there's something to be improved even further. In some cases I do though.
I'd be more than happy to see some more (better performing) non-CTE solutions to get me back to the "right path" 😛
January 23, 2010 at 1:00 pm
tq very much to both of you
January 23, 2010 at 1:10 pm
You're very welcome!
And thank you for posting back. It's always good to get some feedback from the OP (= Original Poster = the person that opened a thread).
It shows the right attitude to become a member of the SSC community.
So, WELCOME ABOARD!
January 23, 2010 at 10:09 pm
The "bad" thing about it: since CTEs made it so easy to use subqueries I think I tend to not spending enough time on trying to find an easier solution for the problem... :ermm:
Agreed.. it makes us a bit lazy :hehe: (but less code always and easy to understand)
I'd be more than happy to see some more (better performing) non-CTE solutions to get me back to the "right path" 😛
😀
---------------------------------------------------------------------------------
January 23, 2010 at 10:11 pm
miss.delinda (1/23/2010)
tq very much to both of you
UR welcome ..:-)
---------------------------------------------------------------------------------
January 23, 2010 at 10:45 pm
Wow! Nicely done, miss.delinda. There are people on this site who have >500 posts that still haven't figured out how to get the best help and you nailed is squarely on your very first post. Needless to say, I'm very impressed. C'mon back anytime.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply