Please Help - 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)

Please Help

Author

Topic

linda9898
Starting Member

28 Posts

Posted - 2008-06-18 : 08:17:06

Hi,

I need a help with building a query that takes data from few rows and put it in one field , as the following example.

i have a table which have flight numbers with the route of the flight

Flight_Number Source Destination
1 London Madrid
1 Madrid Milano
2 London Berlin
1 New York Miami

*** Each flight can have 1 to n rows

what i need is a table which every flight number have only one row
like the following (the Route must be a string field):

Flight_Number Route
1 London-Madrid / Madrid-Milano / New York-Miami
2 London-Berlin

Many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-18 : 08:54:22

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

linda9898
Starting Member

28 Posts

Posted - 2008-06-18 : 11:09:48

Thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2008-06-19 : 01:21:08

quote:
Originally posted by linda9898

Hi,

I need a help with building a query that takes data from few rows and put it in one field , as the following example.

i have a table which have flight numbers with the route of the flight

Flight_Number Source Destination
1 London Madrid
1 Madrid Milano
2 London Berlin
1 New York Miami

*** Each flight can have 1 to n rows

what i need is a table which every flight number have only one row
like the following (the Route must be a string field):

Flight_Number Route
1 London-Madrid / Madrid-Milano / New York-Miami
2 London-Berlin

Many thanks

TRY THIS

DECLARE @Temp TABLE
(
Flight_Number INT,
Source VARCHAR(256),
Destination VARCHAR(256)
)

INSERT INTO @Temp
SELECT 1, 'London', 'Madrid' UNION ALL
SELECT 1, 'Madrid', 'Milano' UNION ALL
SELECT 2, 'London', 'Berlin' UNION ALL
SELECT 1, 'New York', 'Miami'

SELECT * FROM @TEMP

SELECT DISTINCT
Flight_Number,
STUFF((SELECT '/' + SOURCE + '-' + DESTINATION FROM @TEMP T WHERE T.Flight_Number = T1.Flight_Number FOR XML PATH('')), 1, 1, '')
FROM @TEMP T1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 04:14:26

Excallent Raky.
This is exactly what is provided in the link above from madhivanan.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources