Post reply

Dynamic Pivot Columns for Changing column starting points

  • July 24, 2011 at 1:20 am

    #390039

    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

    #1357515

    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?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 24, 2011 at 9:33 am

    #1357536

    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

    #1357545

    The question I have is: do you have the logic already build up how to deal with two identical stop names?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 24, 2011 at 12:18 pm

    #1357558

    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

    #1357563

    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

    #1357569

    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.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 24, 2011 at 3:53 pm

    #1357572

    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

    #1357577

    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

    #1357578

    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...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 24, 2011 at 6:29 pm

    #1357583

    No problem...I look forward to it!

  • July 25, 2011 at 2:27 am

    #1357638

    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?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 25, 2011 at 7:08 am

    #1357746

    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

    #1357956

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


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 25, 2011 at 11:18 am

    #1358001

    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