Need help on query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help on query

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-30 : 12:24:08

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

/*So far, this is my SQL*/
;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

Then, My resultset as follow,
RouteCd | AllCoutCd | AllCoutDesc
-----------------------------------------------------------------------------------
_R00000001 | KT - Kntn - JB | Kuala Terengganu - Kuantan - Johor Bharu
_R00000002 | HPT - JB | Hentian Putra - Johor Bharu

I need to include AllCoutCdNo, so my new resultset as follow
RouteCd | AllCoutCd | AllCoutCdNo | AllCoutDesc
-----------------------------------------------------------------------------------
_R00000001 | KT - Kntn - JB | 3 | Kuala Terengganu - Kuantan - Johor Bharu
_R00000002 | HPT - JB | 2 | Hentian Putra - Johor Bharu

How to adjust my SQL?

Really need help

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-01-30 : 13:19:25

[code]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);

/*So far, this is my SQL*/
;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,
Count(RouteCd) as AllCoutCdNo,
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[/code]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-30 : 13:44:05

tq very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources