April 7, 2011 at 4:54 pm
I am working on a routing system for a transportation type similar to a bus routing system like below.
I have a view which gives me output like this. I need pivoting over schedules where the number of schedules can be of variable quantity.
My query should result in output given below this table. I tried using Case Statements but I had problems with number of rows returned.
StationName ScheduleNumberArrivalTimeDepartureTime
Chicago. IL,Union StationScheduleANull 2:45 AM
Chicago , IL,DownTownScheduleA2:55 AM 3:00 AM
Benton, MI,Harbor ScheduleA8:00 AM Null
Chicago, IL,Union StationScheduleBNull Null
Chicago, IL,DownTownScheduleB6:10 AM 6:20 AM
Benton, IL,Harbor ScheduleB11:00 AM Null
Result Output
StationName ScheduleAScheduleB Arrival/Departure
Chicago. IL,Union Station2:458:00 Departure
Chicago , IL,DownTown2:556:10 Arrival
Chicago , IL,DownTown3:006:20 Departure
Benton, MI,Harbor 8:0011:00 Arrival
Any help will be greaty appreciated
Thanks and regards,
Kunal Uppal
April 7, 2011 at 5:39 pm
Kunal,
If you take a look at the link in my first signature, it'll show you how to present the sample data/DDL in a way we can actually use it to provide better code examples for you. In this case, ones that are tested and will work, rather then pseudocode we think is right.
I assume that Schedule A/B can suddenly end up at C, maybe D, A might disappear tomorrow, and a random Q can show up?
If that's the case, you're looking at building a dynamic statement using the PIVOT operator. We'd definately want some consumable sample data to help walk you through that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 7, 2011 at 10:54 pm
Thanks Craig.
I will build a script tomorrow and will post it again.
April 8, 2011 at 9:41 am
Here are the scripts...
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ScheduleTest]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ScheduleTest](
[StationName] [nvarchar](255) NULL,
[ScheduleNumber] [nvarchar](255) NULL,
[ArrivalTime] [nvarchar](20) NULL,
[DepartureTime] [nvarchar](20) NULL
) ON [PRIMARY]
END
GO
--Insert Scripts For Schedule A
Insert into ScheduleTest Values ('Chicago, IL, (Union Station)', 'ScheduleA', NULL, '02:45')
Insert into ScheduleTest Values ('Chicago, IL, (DownTown)', 'ScheduleA', '02:55', '03:00')
Insert into ScheduleTest Values ('Benton, MI, Harbor', 'ScheduleA', '08:00', NULL) --Benton in this case
--is final destination so departure time is null
-- Insert Scripts for Schedule B (Another Which runs in the morning)
Insert into ScheduleTest Values ('Chicago, IL, (Union Station)', 'ScheduleB', NULL, '06:00')
Insert into ScheduleTest Values ('Chicago, IL, (DownTown)', 'ScheduleB', '06:10', '06:15')
Insert into ScheduleTest Values ('Benton, IL, Harbor', 'ScheduleB', '11:00', NULL)
April 8, 2011 at 9:42 am
Yes Craig.. These Schedules are dynamic and can vary in future
April 8, 2011 at 1:44 pm
How do you decide the logic in the Arrival/Departure column?
Nevermind, I see what you did there. Just started reviewing this, busy day.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 8, 2011 at 2:11 pm
Sorry, running tight on time today or I'd be more explanative of what's going on here.
Go through this code and make sure you understand all the moving parts before this heads to production or you'll be rather frustrated later. Hit us up with any particular questions you might have:
IF OBJECT_ID( 'tempdb..#ScheduleTest') IS NOT NULL
DROP TABLE #ScheduleTest
GO
IF OBJECT_ID( 'tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO
CREATE TABLE #ScheduleTest(
[StationName] [nvarchar](255) NULL,
[ScheduleNumber] [nvarchar](255) NULL,
[ArrivalTime] [nvarchar](20) NULL,
[DepartureTime] [nvarchar](20) NULL
) ON [PRIMARY]
GO
--Insert Scripts For Schedule A
Insert into #ScheduleTest Values ('Chicago, IL, (Union Station)', 'ScheduleA', NULL, '02:45')
Insert into #ScheduleTest Values ('Chicago, IL, (DownTown)', 'ScheduleA', '02:55', '03:00')
Insert into #ScheduleTest Values ('Benton, IL, Harbor', 'ScheduleA', '08:00', NULL) --Benton in this case
--is final destination so departure time is null
-- Insert Scripts for Schedule B (Another Which runs in the morning)
Insert into #ScheduleTest Values ('Chicago, IL, (Union Station)', 'ScheduleB', NULL, '06:00')
Insert into #ScheduleTest Values ('Chicago, IL, (DownTown)', 'ScheduleB', '06:10', '06:15')
Insert into #ScheduleTest Values ('Benton, IL, Harbor', 'ScheduleB', '11:00', NULL)
SELECT
StationName, ScheduleNumber, ArrivalTime AS TimeOfEvent, 'Arrival' AS [Arrival/Departure]
INTO #tmp
FROM
#ScheduleTest
WHERE
ArrivalTime IS NOT NULL
UNION ALL
SELECT
StationName, ScheduleNumber, DepartureTime AS TimeOfEvent, 'Departure' AS [Arrival/Departure]
FROM
#ScheduleTest
WHERE
DepartureTime IS NOT NULL
-- Due to the aggregation requirement of pivot, Only one Schedule/Station combination is allowed.
-- This is the query for the core data. Now, to dynamic this:
SELECT
StationName,
ScheduleA,
ScheduleB,
[Arrival/Departure]
FROM
#tmp AS t
PIVOT
( MAX(TimeOfEvent) FOR ScheduleNumber IN ( ScheduleA, ScheduleB)
) AS pvt
DECLARE @sql nVARCHAR(MAX),
@ScheduleNumber VARCHAR(50),
@PivotInList nVARCHAR(MAX)
SET @sql = 'SELECT StationName, '
SET @PivotInList = ' IN ('
DECLARE ScheduleCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT ScheduleNumber FROM #tmp
OPEN ScheduleCursor
FETCH NEXT FROM ScheduleCursor INTO @ScheduleNumber
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + @ScheduleNumber + ', '
SET @PivotInList = @PivotInList + ' ' + @ScheduleNumber + ','
FETCH NEXT FROM ScheduleCursor INTO @ScheduleNumber
END
CLOSE ScheduleCursor
DEALLOCATE ScheduleCursor
SET @PivotInList = LEFT( @PivotInList, LEN( @PivotInList) - 1 /*Remove the extra comma-space*/) + ')'
PRINT @PivotInList
SET @sql = @sql + '[Arrival/Departure] FROM #tmp AS t PIVOT ( MAX( TimeOfEvent) FOR ScheduleNumber'
+ @PivotInList + ') AS pvt'
PRINT @sql
EXEC sp_executeSQL @sql
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 12, 2011 at 12:57 pm
Thanks Craig. It will take some time to understand the cursor part. I haven't worked on cursors too much, in fact negligible.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply