Query 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

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-03-25 : 23:30:51

Hi,

Here is table DDL

create table #temp
(
flightname varchar(20)
,segmentname varchar(20)
,depatureplace varchar(20)
,arrivalplace varchar(20)
)

insert into #temp
select 'KL123','kl123a','aaaa','bbbb'union all
select 'KL123','kl123b','bbbb','cccc'union all
select 'KL123','kl123c','cccc','dddd'union all
select 'KL126','kl126a','aaaa','bbbb'union all
select 'KL126','kl126b','bbbb','dddd'union all
select 'KL128','kl128','aaaa','dddd'

select * from #temp
flightname segmentname depatureplace arrivalplace
KL123 kl123a aaaa bbbb
KL123 kl123b bbbb cccc
KL123 kl123c cccc dddd
KL126 kl126a aaaa bbbb
KL126 kl126b bbbb dddd
KL128 kl128 aaaa dddd

here flight source is aaaa and destination is dddd

KL123 flight has 3 segments(aaaa-bbbb ,bbbb-cccc,cccc-dddd)
KL126 flight has 2 segments(aaaa-bbbb ,bbbb-dddd)
KL128 has only one segment(aaaa-dddd)

segment means Break journeys

Expected output:
flightname,seg1_segmentname,seg1_depatureplace,seg1_arrivalplace,seg2_segmentname,seg2_depatureplace,seg2_arrivalplace,seg3_segmentname,seg3_depatureplace,seg3_arrivalplace
KL123 kl123a aaaa bbbb kl123b bbbb cccc kl123c cccc dddd
KL126 kl126a aaaa bbbb kl126b bbbb dddd null null null
KL128 kl128 aaaa dddd null null null null null null

here flight can has at most 10 segments.

Please help me in a query.

Thanks

--Ranjit

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-26 : 16:59:17

I am showing one way of doing this below.

There are couple of alternatives as well.

One is to use the PIVOT keyword available in SQL 2005 and later. I don't particularly like it because to me the syntax looks a little non-intuitive. But, that's just me. You might want to check it out.

For what I am showing below and for the PIVOT keyword, you will need to know the cross-tab columns in advance (which in your case you do). If that is not the case, you can use dynamic cross tab, discussed here: http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables or http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

with cte as
(
   select 
      *,
      row_number() over (partition by flightname order by  segmentname) as N1
   from 
      #temp
)
select
   flightname,
   
   max(case when N1 = 1 then segmentname end) as seg1_segmentname,
   max(case when N1 = 1 then depatureplace end) as seg1_depatureplace,
   max(case when N1 = 1 then arrivalplace end) as seg1_arrivalplace,

   max(case when N1 = 2 then segmentname end) as seg2_segmentname,
   max(case when N1 = 2 then depatureplace end) as seg2_depatureplace,
   max(case when N1 = 2 then arrivalplace end) as seg2_arrivalplace
   
   --- and so on
   
from
   cte
group by
   flightname
order by 
   flightname

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-03-27 : 23:27:52

Thank you so much sunitabeck
Its really helpful for me

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources