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);

quote:

---

Originally posted by visakh16

;with cte(RouteCd,CoutCd, CoutDesc) as
(
select d.RouteCd,d.CoutCd, c.CoutDesc
from @tRouteD d
inner join @tCounter c
on c.CoutCd=d.CoutCd
)


select h.RouteCd,
stuff((select ' - ' + CoutCd from cte where RouteCd=h.RouteCd for xml path('')),1,1,''),
stuff((select ' - ' + CoutDesc from cte where RouteCd=h.RouteCd for xml path('')),1,1,'')
from @tRouteH h

---

quote:

---

Originally posted by Delinda

quote:

---

Originally posted by visakh16

;with cte(RouteCd,CoutCd, CoutDesc) as
(
select d.RouteCd,d.CoutCd, c.CoutDesc
from @tRouteD d
inner join @tCounter c
on c.CoutCd=d.CoutCd
)


select h.RouteCd,
stuff((select ' - ' + CoutCd from cte where RouteCd=h.RouteCd for xml path('')),1,3,''),
stuff((select ' - ' + CoutDesc from cte where RouteCd=h.RouteCd for xml path('')),1,3,'')
from @tRouteH h

---

After run above query, my result as follow,
_R00000001 - KT - Kntn - JB - Kuala Terengganu - Kuantan - Johor Bharu
_R00000002 - HPT - JB - Hentian Putra - Johor Bharu

why have - as prefix?

---

quote:

---

Originally posted by Delinda

yes. i got the result.

where RouteCd=h.RouteCd for xml path('')),1,3,''), --- why have a 1,3?

---