July 24, 2011 at 1:20 am
I would like to see if there is a way to overcome my issue using Dynamic Pivot (have SQL Server 2005 and 2008 on different machines). The following CTE is set up with the reader's sanity in mind. The data set I'm working with hits about 60 tables to come up with the results, so I just compiled that in as coherent a manner as possible within the CTE.
I'm going to post the CTE which represents time table information for a bus schedule.
;with routetrips (tripid, [route], direction, stopname, stoptime)
as (select 1, '101', 'SB', 'DTC', '06:15'
union all
select 1, '101', 'SB', 'OLM', '06:20'
union all
select 1, '101', 'SB', 'CLP', '06:27'
union all
select 1, '101', 'SB', 'MTP', '06:29'
union all
select 2, '101', 'NB', 'MTP', '06:30'
union all
select 2, '101', 'NB', 'CLP', '06:35'
union all
select 2, '101', 'NB', 'OLM', '06:42'
union all
select 2, '101', 'NB', 'DTC', '06:44'
union all
select 3, '101', 'SB', 'DTC', '06:45'
union all
select 3, '101', 'SB', 'OLM', '06:50'
union all
select 3, '101', 'SB', 'CLP', '06:57'
union all
select 3, '101', 'SB', 'MTP', '06:59'
union all
select 4, '202', 'LP', 'COE', '10:37'
union all
select 4, '202', 'LP', 'MOB', '10:40'
union all
select 4, '202', 'LP', 'CTC', '10:44'
union all
select 5, '202', 'LP', 'CTC', '10:45'
union all
select 5, '202', 'LP', 'PRM', '10:55'
union all
select 5, '202', 'LP', 'PRT', '11:05'
union all
select 5, '202', 'LP', 'COE', '11:07'
union all
select 5, '202', 'LP', 'MOB', '11:10'
union all
select 5, '202', 'LP', 'CTC', '11:14')
select * from routetrips
Given the result set, I need to be able to pivot dynamically. I do not want to set up a "Stop Name" column because there are about 600 stops in the system that can appear in various routes.
I would like the resultant pivot to be based on 1 route and 1 direction at a time. So I will select the results based on the route and direction.
I would like to see it in the following format; the hiccup really is when you (a) introduce a loop route (min stop and max stop are equal) and (b) when the starting point for one pattern is not the same for the rest within the same route and direction.
For route 101, NB, I would want to see:
Route Direction DTC OLM CLP MTP
101 SB 6:15 6:20 6:27 6:29
101 SB 6:45 6:50 6:57 6:59
For route 101, SB, I would want to see:
Route Direction MTP CLP OLM DTC
101 SB 6:30 6:35 6:42 6:44
For route 202, LP, I would want to see:
Route Direction CTC PRM PRT COE MOB CTC
202 LP -- -- -- 10:37 10:40 10:44
202 LP 10:45 10:55 11:05 11:07 11:10 11:14
I know that route 202 presents a couple problems, just wondering if there's anything out there to overcome that.
Thanks!
July 24, 2011 at 3:15 am
You could use the DynamicCrossTab aproach as described in the related link in my signature.
If you're not familiar with the CrossTab concept, the CrossTab reference would be a good start.
There are dynamic Pivot solutions available, too. But whether you decide to use DynamicPivot or DynamicCrossTab, both will require a dynamic SQL solution.
Edit: Could you provide a hardcoded version for the result for route 202, LP?
July 24, 2011 at 9:33 am
Hi Lutz,
Thanks...I'll check out your link. To answer your question about hard-coding Route 202, LP, I'm guessing you mean that I would do something like this with the result set:
Select Case when [route]='202' and direction='LP' then stoptime end as 'OLP',
etc?
I could do that but it would just be easier and probably less error prone if I did it in Excel. I'm trying to get away from Excel (note: this is for a transit agency that I work for and we've been riddled with costly mistakes every time we send data to a printing company to put our schedules out...we always have done it using spreadsheets and I don't think it's ever exported correctly.)
July 24, 2011 at 11:12 am
The question I have is: do you have the logic already build up how to deal with two identical stop names?
July 24, 2011 at 12:18 pm
Ah, sorry...Sort of. I've run a Row_Number() function to get a sequence, converted to varchar, then concatenated to the stopname in order to differentiate.
Ex:
Select convert(varchar(4), row_number() over (partition by route order by stoptime asc))+'--'+stopname
....
I get mixed results with that. I think I need to figure out the issue with when the first stop of the day isn't the start of the routeconfiguration pattern first then it might make more sense to me.
July 24, 2011 at 1:02 pm
EDIT: Got it in the format I want, but hate the solution...
Here's an example of where I am so far. I know I have to convert my array of hard-coded columns into T-SQL. I just need to figure out how to order the columns properly first. Here's the query and the output so far:
;with routetrips (tripid, [route], direction, stopname, stoptime)
as (select 1, '101', 'SB', 'DTC', '06:15'
union all
select 1, '101', 'SB', 'OLM', '06:20'
union all
select 1, '101', 'SB', 'CLP', '06:27'
union all
select 1, '101', 'SB', 'MTP', '06:29'
union all
select 2, '101', 'NB', 'MTP', '06:30'
union all
select 2, '101', 'NB', 'CLP', '06:35'
union all
select 2, '101', 'NB', 'OLM', '06:42'
union all
select 2, '101', 'NB', 'DTC', '06:44'
union all
select 3, '101', 'SB', 'DTC', '06:45'
union all
select 3, '101', 'SB', 'OLM', '06:50'
union all
select 3, '101', 'SB', 'CLP', '06:57'
union all
select 3, '101', 'SB', 'MTP', '06:59'
union all
select 4, '202', 'LP', 'COE', '10:37'
union all
select 4, '202', 'LP', 'MOB', '10:40'
union all
select 4, '202', 'LP', 'CTC', '10:44'
union all
select 5, '202', 'LP', 'CTC', '10:45'
union all
select 5, '202', 'LP', 'PRM', '10:55'
union all
select 5, '202', 'LP', 'PRT', '11:05'
union all
select 5, '202', 'LP', 'COE', '11:07'
union all
select 5, '202', 'LP', 'MOB', '11:10'
union all
select 5, '202', 'LP', 'CTC', '11:14')
select[route],
tripid,
case when MIN([1-COE])<min([1-CTC]) then null else min([1-CTC]) end as [1-CTC],
min([1-PRM]) as [1-PRM],
min([1-PRT]) as [1-PRT],
min([1-COE]) as [1-COE],
min([1-MOB]) as [1-MOB],
case when MIN([1-COE])<min([1-CTC]) then MAX([1-CTC]) else min([2-CTC]) end as [2-CTC]
from (
select[route],
tripid,
case when stopname='1-CTC' then stoptime else null end as '1-CTC',
case when stopname='1-PRM' then stoptime else null end as '1-PRM',
case when stopname='1-PRT' then stoptime else null end as '1-PRT',
case when stopname='1-COE' then stoptime else null end as '1-COE',
case when stopname='1-MOB' then stoptime else null end as '1-MOB',
case when stopname='2-CTC' then stoptime else null end as '2-CTC'
From (
select tripid, [route], direction, Row+'-'+stopname as stopname, stoptime
from (
select tripid, [route], direction, stopname, stoptime,
convert(varchar(4),Row_Number() over (partition by tripid, direction, stopname order by stoptime)) as row
from routetrips
where [route]='202'
group by tripid, stoptime, [route], direction, stopname) a ) Rte ) agg
group by [route], tripid [/code]
Results:
route1-CTC1-PRM1-PRT1-COE1-MOB2-CTC
202NULLNULLNULL10:3710:4010:44
20210:4510:5511:0511:0711:1011:14
The two problems with this are 1) 1-COE is the 1st stop on the 1st trip, but CTC is the starting position for that route, so 1-CTC should always be the 1st column. 2) It's possible to have a stop show up 3 times or more on a route, so it's definitely not ideal to have to hard code the route orders this way.
July 24, 2011 at 3:19 pm
Do you have a table holding all stops per trip in the required sequence? (including the sort order)? Something like route_stop_id (identity column for that table), [route], direction, stopnumber, stopdescription.
The routetrips table then would only have four columns: routetripID (identity column for that table),tripid,route_stop_id, stoptime.
This would not only be very helpful to solve the puzzle but also more normalized than what you have so far...
The columns for the pivoted data would be based on the new table and the rows would be based on the (modified) routetrips table.
July 24, 2011 at 3:53 pm
Yes, I suppose it would be helpful to post the full DDL. The problem is that the database I'm using is very multi-faceted and takes a lot of tables to produce the schedule information. I was trying to pull everything together for readability, but guess it made it harder in the long run.
I'll try to pull everything together today and post.
I appreciate your help!
July 24, 2011 at 5:35 pm
Ok...spent some time to condense the tables to the absolute minimum and still have the sequences etc. Keep in mind that the sequence is not consecutive numbering; this is because the query doesn't show all stops, just what we call, "timepoints" there are many other stops in between these stops...that would be a lot of unnecessary detail:
create table trp (periodid int, dayofweekid int, tripid int, [route] varchar(5), routedirid int, patternid int);
create table ttime (tripid int, [type] int, [time] time, num int);
create table routestop (periodid int, routedirid int, num int, sequence int, stpid int);
create table pttrn (periodid int, patternid int, routedirid int, pttrn varchar(5));
create table routeline (periodid int, routelineid int, routedirid int, direction varchar(5));
create table stp (stpid int, stpabbr varchar(10));
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65418,2,7750,1796);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65419,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65420,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65421,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65422,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65423,2,7750,1944);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65424,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65425,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65426,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65427,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65428,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65429,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65430,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65431,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65432,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65433,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65434,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65435,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65436,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65437,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65438,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65439,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65440,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65441,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65442,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65447,2,7750,1797);
insert into trp (periodid, dayofweekid, tripid, [route], routedirid, patternid) values (44,8,65449,2,7750,1797);
insert into ttime (tripid, [type], [time], num) values (65418,1,'06:02',1);
insert into ttime (tripid, [type], [time], num) values (65418,3,'06:22',3);
insert into ttime (tripid, [type], [time], num) values (65418,3,'06:24',4);
insert into ttime (tripid, [type], [time], num) values (65418,3,'06:26',5);
insert into ttime (tripid, [type], [time], num) values (65418,3,'06:40',7);
insert into ttime (tripid, [type], [time], num) values (65418,7,'06:40',7);
insert into ttime (tripid, [type], [time], num) values (65418,8,'06:40',7);
insert into ttime (tripid, [type], [time], num) values (65418,3,'06:30',8);
insert into ttime (tripid, [type], [time], num) values (65419,3,'16:15',1);
insert into ttime (tripid, [type], [time], num) values (65419,3,'16:19',2);
insert into ttime (tripid, [type], [time], num) values (65419,3,'16:22',3);
insert into ttime (tripid, [type], [time], num) values (65419,3,'16:24',4);
insert into ttime (tripid, [type], [time], num) values (65419,3,'16:26',5);
insert into ttime (tripid, [type], [time], num) values (65419,3,'16:40',7);
insert into ttime (tripid, [type], [time], num) values (65419,7,'16:40',7);
insert into ttime (tripid, [type], [time], num) values (65419,8,'16:40',7);
insert into ttime (tripid, [type], [time], num) values (65419,3,'16:30',8);
insert into ttime (tripid, [type], [time], num) values (65420,3,'16:45',1);
insert into ttime (tripid, [type], [time], num) values (65420,3,'16:49',2);
insert into ttime (tripid, [type], [time], num) values (65420,3,'16:52',3);
insert into ttime (tripid, [type], [time], num) values (65420,3,'16:54',4);
insert into ttime (tripid, [type], [time], num) values (65420,3,'16:56',5);
insert into ttime (tripid, [type], [time], num) values (65420,3,'17:10',7);
insert into ttime (tripid, [type], [time], num) values (65420,7,'17:10',7);
insert into ttime (tripid, [type], [time], num) values (65420,8,'17:10',7);
insert into ttime (tripid, [type], [time], num) values (65420,3,'17:00',8);
insert into ttime (tripid, [type], [time], num) values (65421,3,'17:15',1);
insert into ttime (tripid, [type], [time], num) values (65421,3,'17:19',2);
insert into ttime (tripid, [type], [time], num) values (65421,3,'17:22',3);
insert into ttime (tripid, [type], [time], num) values (65421,3,'17:24',4);
insert into ttime (tripid, [type], [time], num) values (65421,3,'17:26',5);
insert into ttime (tripid, [type], [time], num) values (65421,3,'17:40',7);
insert into ttime (tripid, [type], [time], num) values (65421,7,'17:40',7);
insert into ttime (tripid, [type], [time], num) values (65421,8,'17:40',7);
insert into ttime (tripid, [type], [time], num) values (65421,3,'17:30',8);
insert into ttime (tripid, [type], [time], num) values (65422,3,'17:45',1);
insert into ttime (tripid, [type], [time], num) values (65422,3,'17:49',2);
insert into ttime (tripid, [type], [time], num) values (65422,3,'17:52',3);
insert into ttime (tripid, [type], [time], num) values (65422,3,'17:54',4);
insert into ttime (tripid, [type], [time], num) values (65422,3,'17:56',5);
insert into ttime (tripid, [type], [time], num) values (65422,3,'18:10',7);
insert into ttime (tripid, [type], [time], num) values (65422,7,'18:10',7);
insert into ttime (tripid, [type], [time], num) values (65422,8,'18:10',7);
insert into ttime (tripid, [type], [time], num) values (65422,3,'18:00',8);
insert into ttime (tripid, [type], [time], num) values (65423,2,'18:50',1);
insert into ttime (tripid, [type], [time], num) values (65423,3,'18:15',1);
insert into ttime (tripid, [type], [time], num) values (65423,3,'18:19',2);
insert into ttime (tripid, [type], [time], num) values (65423,3,'18:22',3);
insert into ttime (tripid, [type], [time], num) values (65423,3,'18:24',4);
insert into ttime (tripid, [type], [time], num) values (65423,3,'18:26',5);
insert into ttime (tripid, [type], [time], num) values (65423,3,'18:30',8);
insert into ttime (tripid, [type], [time], num) values (65424,3,'06:45',1);
insert into ttime (tripid, [type], [time], num) values (65424,3,'06:49',2);
insert into ttime (tripid, [type], [time], num) values (65424,3,'06:52',3);
insert into ttime (tripid, [type], [time], num) values (65424,3,'06:54',4);
insert into ttime (tripid, [type], [time], num) values (65424,3,'06:56',5);
insert into ttime (tripid, [type], [time], num) values (65424,3,'07:10',7);
insert into ttime (tripid, [type], [time], num) values (65424,7,'07:10',7);
insert into ttime (tripid, [type], [time], num) values (65424,8,'07:10',7);
insert into ttime (tripid, [type], [time], num) values (65424,3,'07:00',8);
insert into ttime (tripid, [type], [time], num) values (65425,3,'07:15',1);
insert into ttime (tripid, [type], [time], num) values (65425,3,'07:19',2);
insert into ttime (tripid, [type], [time], num) values (65425,3,'07:22',3);
insert into ttime (tripid, [type], [time], num) values (65425,3,'07:24',4);
insert into ttime (tripid, [type], [time], num) values (65425,3,'07:26',5);
insert into ttime (tripid, [type], [time], num) values (65425,3,'07:40',7);
insert into ttime (tripid, [type], [time], num) values (65425,7,'07:40',7);
insert into ttime (tripid, [type], [time], num) values (65425,8,'07:40',7);
insert into ttime (tripid, [type], [time], num) values (65425,3,'07:30',8);
insert into ttime (tripid, [type], [time], num) values (65426,3,'07:45',1);
insert into ttime (tripid, [type], [time], num) values (65426,3,'07:49',2);
insert into ttime (tripid, [type], [time], num) values (65426,3,'07:52',3);
insert into ttime (tripid, [type], [time], num) values (65426,3,'07:54',4);
insert into ttime (tripid, [type], [time], num) values (65426,3,'07:56',5);
insert into ttime (tripid, [type], [time], num) values (65426,3,'08:10',7);
insert into ttime (tripid, [type], [time], num) values (65426,7,'08:10',7);
insert into ttime (tripid, [type], [time], num) values (65426,8,'08:10',7);
insert into ttime (tripid, [type], [time], num) values (65426,3,'08:00',8);
insert into ttime (tripid, [type], [time], num) values (65427,3,'08:15',1);
insert into ttime (tripid, [type], [time], num) values (65427,3,'08:19',2);
insert into ttime (tripid, [type], [time], num) values (65427,3,'08:22',3);
insert into ttime (tripid, [type], [time], num) values (65427,3,'08:24',4);
insert into ttime (tripid, [type], [time], num) values (65427,3,'08:26',5);
insert into ttime (tripid, [type], [time], num) values (65427,3,'08:40',7);
insert into ttime (tripid, [type], [time], num) values (65427,7,'08:40',7);
insert into ttime (tripid, [type], [time], num) values (65427,8,'08:40',7);
insert into ttime (tripid, [type], [time], num) values (65427,3,'08:30',8);
insert into ttime (tripid, [type], [time], num) values (65428,3,'09:45',1);
insert into ttime (tripid, [type], [time], num) values (65428,3,'09:49',2);
insert into ttime (tripid, [type], [time], num) values (65428,3,'09:52',3);
insert into ttime (tripid, [type], [time], num) values (65428,3,'09:54',4);
insert into ttime (tripid, [type], [time], num) values (65428,3,'09:56',5);
insert into ttime (tripid, [type], [time], num) values (65428,3,'10:10',7);
insert into ttime (tripid, [type], [time], num) values (65428,7,'10:10',7);
insert into ttime (tripid, [type], [time], num) values (65428,8,'10:10',7);
insert into ttime (tripid, [type], [time], num) values (65428,3,'10:00',8);
insert into ttime (tripid, [type], [time], num) values (65429,3,'10:15',1);
insert into ttime (tripid, [type], [time], num) values (65429,3,'10:19',2);
insert into ttime (tripid, [type], [time], num) values (65429,3,'10:22',3);
insert into ttime (tripid, [type], [time], num) values (65429,3,'10:24',4);
insert into ttime (tripid, [type], [time], num) values (65429,3,'10:26',5);
insert into ttime (tripid, [type], [time], num) values (65429,3,'10:40',7);
insert into ttime (tripid, [type], [time], num) values (65429,7,'10:40',7);
insert into ttime (tripid, [type], [time], num) values (65429,8,'10:40',7);
insert into ttime (tripid, [type], [time], num) values (65429,3,'10:30',8);
insert into ttime (tripid, [type], [time], num) values (65430,3,'10:45',1);
insert into ttime (tripid, [type], [time], num) values (65430,3,'10:49',2);
insert into ttime (tripid, [type], [time], num) values (65430,3,'10:52',3);
insert into ttime (tripid, [type], [time], num) values (65430,3,'10:54',4);
insert into ttime (tripid, [type], [time], num) values (65430,3,'10:56',5);
insert into ttime (tripid, [type], [time], num) values (65430,3,'11:10',7);
insert into ttime (tripid, [type], [time], num) values (65430,7,'11:10',7);
insert into ttime (tripid, [type], [time], num) values (65430,8,'11:10',7);
insert into ttime (tripid, [type], [time], num) values (65430,3,'11:00',8);
insert into ttime (tripid, [type], [time], num) values (65431,3,'11:15',1);
insert into ttime (tripid, [type], [time], num) values (65431,3,'11:19',2);
insert into ttime (tripid, [type], [time], num) values (65431,3,'11:22',3);
insert into ttime (tripid, [type], [time], num) values (65431,3,'11:24',4);
insert into ttime (tripid, [type], [time], num) values (65431,3,'11:26',5);
insert into ttime (tripid, [type], [time], num) values (65431,3,'11:40',7);
insert into ttime (tripid, [type], [time], num) values (65431,7,'11:40',7);
insert into ttime (tripid, [type], [time], num) values (65431,8,'11:40',7);
insert into ttime (tripid, [type], [time], num) values (65431,3,'11:30',8);
insert into ttime (tripid, [type], [time], num) values (65432,3,'11:45',1);
insert into ttime (tripid, [type], [time], num) values (65432,3,'11:49',2);
insert into ttime (tripid, [type], [time], num) values (65432,3,'11:52',3);
insert into ttime (tripid, [type], [time], num) values (65432,3,'11:54',4);
insert into ttime (tripid, [type], [time], num) values (65432,3,'11:56',5);
insert into ttime (tripid, [type], [time], num) values (65432,3,'12:10',7);
insert into ttime (tripid, [type], [time], num) values (65432,7,'12:10',7);
insert into ttime (tripid, [type], [time], num) values (65432,8,'12:10',7);
insert into ttime (tripid, [type], [time], num) values (65432,3,'12:00',8);
insert into ttime (tripid, [type], [time], num) values (65433,3,'12:15',1);
insert into ttime (tripid, [type], [time], num) values (65433,3,'12:19',2);
insert into ttime (tripid, [type], [time], num) values (65433,3,'12:22',3);
insert into ttime (tripid, [type], [time], num) values (65433,3,'12:24',4);
insert into ttime (tripid, [type], [time], num) values (65433,3,'12:26',5);
insert into ttime (tripid, [type], [time], num) values (65433,3,'12:40',7);
insert into ttime (tripid, [type], [time], num) values (65433,7,'12:40',7);
insert into ttime (tripid, [type], [time], num) values (65433,8,'12:40',7);
insert into ttime (tripid, [type], [time], num) values (65433,3,'12:30',8);
insert into ttime (tripid, [type], [time], num) values (65434,3,'12:45',1);
insert into ttime (tripid, [type], [time], num) values (65434,3,'12:49',2);
insert into ttime (tripid, [type], [time], num) values (65434,3,'12:52',3);
insert into ttime (tripid, [type], [time], num) values (65434,3,'12:54',4);
insert into ttime (tripid, [type], [time], num) values (65434,3,'12:56',5);
insert into ttime (tripid, [type], [time], num) values (65434,3,'13:10',7);
insert into ttime (tripid, [type], [time], num) values (65434,7,'13:10',7);
insert into ttime (tripid, [type], [time], num) values (65434,8,'13:10',7);
insert into ttime (tripid, [type], [time], num) values (65434,3,'13:00',8);
insert into ttime (tripid, [type], [time], num) values (65435,3,'13:15',1);
insert into ttime (tripid, [type], [time], num) values (65435,3,'13:19',2);
insert into ttime (tripid, [type], [time], num) values (65435,3,'13:22',3);
insert into ttime (tripid, [type], [time], num) values (65435,3,'13:24',4);
insert into ttime (tripid, [type], [time], num) values (65435,3,'13:26',5);
insert into ttime (tripid, [type], [time], num) values (65435,3,'13:40',7);
insert into ttime (tripid, [type], [time], num) values (65435,7,'13:40',7);
insert into ttime (tripid, [type], [time], num) values (65435,8,'13:40',7);
insert into ttime (tripid, [type], [time], num) values (65435,3,'13:30',8);
insert into ttime (tripid, [type], [time], num) values (65436,3,'13:45',1);
insert into ttime (tripid, [type], [time], num) values (65436,3,'13:49',2);
insert into ttime (tripid, [type], [time], num) values (65436,3,'13:52',3);
insert into ttime (tripid, [type], [time], num) values (65436,3,'13:54',4);
insert into ttime (tripid, [type], [time], num) values (65436,3,'13:56',5);
insert into ttime (tripid, [type], [time], num) values (65436,3,'14:10',7);
insert into ttime (tripid, [type], [time], num) values (65436,7,'14:10',7);
insert into ttime (tripid, [type], [time], num) values (65436,8,'14:10',7);
insert into ttime (tripid, [type], [time], num) values (65436,3,'14:00',8);
insert into ttime (tripid, [type], [time], num) values (65437,3,'14:15',1);
insert into ttime (tripid, [type], [time], num) values (65437,3,'14:19',2);
insert into ttime (tripid, [type], [time], num) values (65437,3,'14:22',3);
insert into ttime (tripid, [type], [time], num) values (65437,3,'14:24',4);
insert into ttime (tripid, [type], [time], num) values (65437,3,'14:26',5);
insert into ttime (tripid, [type], [time], num) values (65437,3,'14:40',7);
insert into ttime (tripid, [type], [time], num) values (65437,7,'14:40',7);
insert into ttime (tripid, [type], [time], num) values (65437,8,'14:40',7);
insert into ttime (tripid, [type], [time], num) values (65437,3,'14:30',8);
insert into ttime (tripid, [type], [time], num) values (65438,3,'14:45',1);
insert into ttime (tripid, [type], [time], num) values (65438,3,'14:49',2);
insert into ttime (tripid, [type], [time], num) values (65438,3,'14:52',3);
insert into ttime (tripid, [type], [time], num) values (65438,3,'14:54',4);
insert into ttime (tripid, [type], [time], num) values (65438,3,'14:56',5);
insert into ttime (tripid, [type], [time], num) values (65438,3,'15:10',7);
insert into ttime (tripid, [type], [time], num) values (65438,7,'15:10',7);
insert into ttime (tripid, [type], [time], num) values (65438,8,'15:10',7);
insert into ttime (tripid, [type], [time], num) values (65438,3,'15:00',8);
insert into ttime (tripid, [type], [time], num) values (65439,3,'15:15',1);
insert into ttime (tripid, [type], [time], num) values (65439,3,'15:19',2);
insert into ttime (tripid, [type], [time], num) values (65439,3,'15:22',3);
insert into ttime (tripid, [type], [time], num) values (65439,3,'15:24',4);
insert into ttime (tripid, [type], [time], num) values (65439,3,'15:26',5);
insert into ttime (tripid, [type], [time], num) values (65439,3,'15:40',7);
insert into ttime (tripid, [type], [time], num) values (65439,7,'15:40',7);
insert into ttime (tripid, [type], [time], num) values (65439,8,'15:40',7);
insert into ttime (tripid, [type], [time], num) values (65439,3,'15:30',8);
insert into ttime (tripid, [type], [time], num) values (65440,3,'15:45',1);
insert into ttime (tripid, [type], [time], num) values (65440,3,'15:49',2);
insert into ttime (tripid, [type], [time], num) values (65440,3,'15:52',3);
insert into ttime (tripid, [type], [time], num) values (65440,3,'15:54',4);
insert into ttime (tripid, [type], [time], num) values (65440,3,'15:56',5);
insert into ttime (tripid, [type], [time], num) values (65440,3,'16:10',7);
insert into ttime (tripid, [type], [time], num) values (65440,7,'16:10',7);
insert into ttime (tripid, [type], [time], num) values (65440,8,'16:10',7);
insert into ttime (tripid, [type], [time], num) values (65440,3,'16:00',8);
insert into ttime (tripid, [type], [time], num) values (65441,3,'08:45',1);
insert into ttime (tripid, [type], [time], num) values (65441,3,'08:49',2);
insert into ttime (tripid, [type], [time], num) values (65441,3,'08:52',3);
insert into ttime (tripid, [type], [time], num) values (65441,3,'08:54',4);
insert into ttime (tripid, [type], [time], num) values (65441,3,'08:56',5);
insert into ttime (tripid, [type], [time], num) values (65441,3,'09:10',7);
insert into ttime (tripid, [type], [time], num) values (65441,7,'09:10',7);
insert into ttime (tripid, [type], [time], num) values (65441,8,'09:10',7);
insert into ttime (tripid, [type], [time], num) values (65441,3,'09:00',8);
insert into ttime (tripid, [type], [time], num) values (65442,3,'09:15',1);
insert into ttime (tripid, [type], [time], num) values (65442,3,'09:19',2);
insert into ttime (tripid, [type], [time], num) values (65442,3,'09:22',3);
insert into ttime (tripid, [type], [time], num) values (65442,3,'09:24',4);
insert into ttime (tripid, [type], [time], num) values (65442,3,'09:26',5);
insert into ttime (tripid, [type], [time], num) values (65442,3,'09:40',7);
insert into ttime (tripid, [type], [time], num) values (65442,7,'09:40',7);
insert into ttime (tripid, [type], [time], num) values (65442,8,'09:40',7);
insert into ttime (tripid, [type], [time], num) values (65442,3,'09:30',8);
insert into ttime (tripid, [type], [time], num) values (65447,3,'09:45',1);
insert into ttime (tripid, [type], [time], num) values (65447,3,'09:49',2);
insert into ttime (tripid, [type], [time], num) values (65447,3,'09:52',3);
insert into ttime (tripid, [type], [time], num) values (65447,3,'09:54',4);
insert into ttime (tripid, [type], [time], num) values (65447,3,'09:56',5);
insert into ttime (tripid, [type], [time], num) values (65447,3,'10:10',7);
insert into ttime (tripid, [type], [time], num) values (65447,7,'10:10',7);
insert into ttime (tripid, [type], [time], num) values (65447,8,'10:10',7);
insert into ttime (tripid, [type], [time], num) values (65447,3,'10:00',8);
insert into ttime (tripid, [type], [time], num) values (65449,3,'09:15',1);
insert into ttime (tripid, [type], [time], num) values (65449,3,'09:19',2);
insert into ttime (tripid, [type], [time], num) values (65449,3,'09:22',3);
insert into ttime (tripid, [type], [time], num) values (65449,3,'09:24',4);
insert into ttime (tripid, [type], [time], num) values (65449,3,'09:26',5);
insert into ttime (tripid, [type], [time], num) values (65449,3,'09:40',7);
insert into ttime (tripid, [type], [time], num) values (65449,7,'09:40',7);
insert into ttime (tripid, [type], [time], num) values (65449,8,'09:40',7);
insert into ttime (tripid, [type], [time], num) values (65449,3,'09:30',8);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,1,1,10);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,51,2,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,52,3,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,25,4,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,2,5,341);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,29,6,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,30,7,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,31,8,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,3,9,318);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,33,10,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,34,11,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,35,12,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,36,13,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,4,14,13);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,38,15,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,5,16,281);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,40,17,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,41,18,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,42,19,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,43,20,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,44,21,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,45,22,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,46,23,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,8,24,239);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,50,25,0);
insert into routestop (periodid, routedirid, num, sequence, Stpid) values (44,7750,7,26,10);
insert into pttrn (periodid, patternid, routedirid, pttrn) values (44,1796,7750,'LP1');
insert into pttrn (periodid, patternid, routedirid, pttrn) values (44,1797,7750,'LP2');
insert into pttrn (periodid, patternid, routedirid, pttrn) values (44,1944,7750,'LP4');
insert into routeline (periodid, routelineid, routedirid, direction) values (44,775,7750,'LOOP');
insert into Stp (stpid, stpabbr) values (10,'CTC/CSU');
insert into Stp (stpid, stpabbr) values (13,'PROOVEWB');
insert into Stp (stpid, stpabbr) values (239,'ELICITEB');
insert into Stp (stpid, stpabbr) values (281,'ELIOVEEB');
insert into Stp (stpid, stpabbr) values (318,'PROTAFWB');
insert into Stp (stpid, stpabbr) values (341,'PROSHIWB');
and the basic select statement without any aggregation:
select t.tripid,
mp.pttrn,
[Route],
rl.direction,
n.stpabbr,
rs.sequence,
tt.[time]
fromtrp t
jointtime tt on tt.tripid=t.tripid and tt.[type]=3
joinroutestop rs on rs.periodid=t.periodid and rs.routedirid=t.routedirid and rs.num=tt.num
joinpttrn mp on mp.periodid=t.periodid and mp.patternid=t.patternid and mp.routedirid=t.routedirid
joinrouteline rl on rl.periodid=t.periodid and rl.routedirid=t.routedirid
joinstp n on n.stpid=rs.stpid
where
T.periodid=44 and
T.dayofweekid=8 and
T.[route]=2
order by tt.[time];
July 24, 2011 at 5:46 pm
It's a little late over here in Europe (almost 2am), so I'll have a look at it tomorrow if there is no reply...
July 24, 2011 at 6:29 pm
No problem...I look forward to it!
July 25, 2011 at 2:27 am
Ok, here's the static version based on your sample data:
; WITH routeorder as
(
SELECT
t.*,
rs.stpid,
rs.num,
-- assign a sequential number to each stop for a specific route
-- regardless whether a specific tripid stop there or not
ROW_NUMBER() OVER(partition by t.periodid, tripid ORDER BY sequence) AS pos
FROM routestop rs
INNER JOIN trp t on rs.periodid=t.periodid AND rs.routedirid=t.routedirid
WHERE stpid <>0
AND t.periodid=44
ANDt.dayofweekid=8
ANDt.[route]=2
)
SELECT
ro.tripid,
mp.pttrn,
[Route],
rl.direction,
MAX(CASE WHEN pos=1 THEN CONVERT(CHAR(5),tt.[time],114) ELSE ' --- ' END) AS [CTC/CSU],
MAX(CASE WHEN pos=2 THEN CONVERT(CHAR(5),tt.[time],114) ELSE ' --- ' END) AS [PROSHIWB],
MAX(CASE WHEN pos=3 THEN CONVERT(CHAR(5),tt.[time],114) ELSE ' --- ' END) AS [PROTAFWB],
MAX(CASE WHEN pos=4 THEN CONVERT(CHAR(5),tt.[time],114) ELSE ' --- ' END) AS [PROOVEWB],
MAX(CASE WHEN pos=5 THEN CONVERT(CHAR(5),tt.[time],114) ELSE ' --- ' END) AS [ELIOVEEB],
MAX(CASE WHEN pos=6 THEN CONVERT(CHAR(5),tt.[time],114) ELSE ' --- ' END) AS [ELICITEB],
MAX(CASE WHEN pos=7 THEN CONVERT(CHAR(5),tt.[time],114) ELSE ' --- ' END) AS [CTC/CSU]
from routeorder ro
join ttime tt on tt.tripid=ro.tripid and tt.[type]=3 AND ro.num=tt.num
join pttrn mp on mp.periodid=ro.periodid and mp.patternid=ro.patternid and mp.routedirid=ro.routedirid
join routeline rl on rl.periodid=ro.periodid and rl.routedirid=ro.routedirid
join stp n on n.stpid=ro.stpid
GROUP BY ro.tripid,mp.pttrn,[Route],rl.direction
order by ro.tripid;
Please verify if the results are correct. If yes, have a look at the DynamicCrossTab in my signature and give it a try to change the code into a dynamic SQL version.
If you get stuck, please post what you have and we'll work from there.
As a side note: stuff like that usually should be done at the presentation layer. What's the reason not to do so?
July 25, 2011 at 7:08 am
Thanks...thi sis starting to look good. I set up a scenario last night that was very close, but the stops were in the wrong order. Your example looks good, but each row needs to be in time order too. Here's the desired output:
65418LP12LOOP --- --- 6:226:246:266:306:40
65424LP22LOOP6:456:496:526:546:567:007:10
65425LP22LOOP7:157:197:227:247:267:307:40
65426LP22LOOP7:457:497:527:547:568:008:10
65427LP22LOOP8:158:198:228:248:268:308:40
65441LP22LOOP8:458:498:528:548:569:009:10
65442LP22LOOP9:159:199:229:249:269:309:40
65449LP22LOOP9:159:199:229:249:269:309:40
65447LP22LOOP9:459:499:529:549:5610:0010:10
65428LP22LOOP9:459:499:529:549:5610:0010:10
65429LP22LOOP10:1510:1910:2210:2410:2610:3010:40
65430LP22LOOP10:4510:4910:5210:5410:5611:0011:10
65431LP22LOOP11:1511:1911:2211:2411:2611:3011:40
65432LP22LOOP11:4511:4911:5211:5411:5612:0012:10
65433LP22LOOP12:1512:1912:2212:2412:2612:3012:40
65434LP22LOOP12:4512:4912:5212:5412:5613:0013:10
65435LP22LOOP13:1513:1913:2213:2413:2613:3013:40
65436LP22LOOP13:4513:4913:5213:5413:5614:0014:10
65437LP22LOOP14:1514:1914:2214:2414:2614:3014:40
65438LP22LOOP14:4514:4914:5214:5414:5615:0015:10
65439LP22LOOP15:1515:1915:2215:2415:2615:3015:40
65440LP22LOOP15:4515:4915:5215:5415:5616:0016:10
65419LP22LOOP16:1516:1916:2216:2416:2616:3016:40
65420LP22LOOP16:4516:4916:5216:5416:5617:0017:10
65421LP22LOOP17:1517:1917:2217:2417:2617:3017:40
65422LP22LOOP17:4517:4917:5217:5417:5618:0018:10
65423LP42LOOP18:1518:1918:2218:2418:2618:30 ---
I realize this will be difficult because the tripid's aren't necessarily in time order; so I need to set up some type of row_number() assignment in order to handle a time ordered tripid.
To answer your question regarding the purpose for doing this in SQL, I mentioned in an earlier post that we've been doing this in Excel; but it's been riddled with errors due to the nature of the process we have. We have about 20 people who have to have a look at the data before it goes to the publisher, then we get it back. Invariably, they've made some changes and it's very difficult to find them.
I want to be able to pull the source data, export it once in the format it needs to be and be done with it 🙂 The errors can cost my organization several thousand dollars at a time.
July 25, 2011 at 10:37 am
to get the ort order right, replace "order by ro.tripid;" with "order by MIN(tt.[time]) ;"
Regarding the dynamic part: would it be an option to have the "real" column names in the first row and call the columns 1,2,3... and so on?
If that's an option we don't need to go the dynamic route. Would be a lot easier to maintain....
But the disadvantage would be to code for the max possible number of stations (+ a few extra). This would lead to the following example (coded for 10 stops)
tripid pttrn Route direction 12345678910
-------------------------------------------------------------------------------------------------------------
tripid pttrn Route direction CTC/CSU PROSHIW PROTAFB PROOVEB ELIOVEB ELICIEB CTC/CSUEMPTYEMPTYEMPTY
65418 LP1 2 LOOP --- ---06:2206:2406:2606:3006:40 --- --- ---
65424 LP2 2 LOOP 06:45 06:4906:5206:5406:5607:0007:10 --- --- ---
65425 LP2 2 LOOP 07:15 07:1907:2207:2407:2607:3007:40 --- --- ---
65426 LP2 2 LOOP 07:45 07:4907:5207:5407:5608:0008:10 --- --- ---
65427 LP2 2 LOOP 08:15 08:1908:2208:2408:2608:3008:40 --- --- ---
July 25, 2011 at 11:18 am
Lutz,
That works beautifully! Thank you so much. As for the static code with the case expressions, the problem is that I have some routes with about 70 stops on a configuration, so it becomes a bit difficult to manage.
I'll work on the dynamic code and see which one works out better.
Thanks again for your help.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply