SQL question : How do I pivot mutiple results to single row?

Question

I have table in data base name "train delay, with columns

train number(int),
DelayTime(int),
DelayReason(nchar)

so the train may have more than one delaytime for each delay reason, for example:

trainnumber,Delaytime,DelayReason
1          ,5 sec    ,x
1          ,10 sec    ,Z
1          ,70 sec    ,TY

I want to create a crystal report with the following design:

trainnumber, delaytime 1,delay reason 1 ,delaytime 2, delay reason 2,delaytime 3,delay reason 3

But I don't know the query which will get me this result.

I have tried this:

select delaytime from dbo.traindelay

But the output looks like this:

Delaytime
5
10
70

And I don't want that. I want something like this:

delaytime1 ,delaytime2 ,delaytime3

Answer 1

Hi,

First, I'll propose a new structure by adding a column called Id so now you have 2 tables :

• Train(int Id, string Name)
• TrainDelay (int Id, int TrainId, int DelayTime, nchar DelayReason

The SQL Query to have a maximum of 3 delays per train is :

select 
  t.Name, 
  d1.DelayTime   as Delay1, 
  d1.DelayReason as Reason1,
  d2.DelayTime   as Delay2, 
  d2.DelayReason as Reason2,
  d3.DelayTime   as Delay3, 
  d3.DelayReason as Reason3,
from Train as t
left join TrainDelay as d1 on d1.TrainId = t.Id 
left join TrainDelay as d2 on d2.TrainId = t.Id and d2.Id > d1.Id
left join TrainDelay as d3 on d3.TrainId = t.Id and d3.Id > d2.Id

Note that if you have more than 3 delays for the same train, then you would have multiple results per train with duplicated records. You can add more joins but it would get extremelly slow if your table is big.