Блог

Я работаю над системой маршрутизации для типа транспорта, аналогичной системе маршрутизации автобусов, как показано ниже. У меня есть представление, которое выдает мне выходные данные. Мне нужно выполнить поворот по расписаниям, где количество расписаний может быть переменной величиной.

Результатом моего запроса должен быть результат, приведенный ниже на изображении. Я пытался использовать операторы Case, но у меня возникли проблемы с количеством возвращаемых строк.

Вот сценарии для создания таблицы и справочных данных:

 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)
  

Я не думаю, что смогу использовать поворот в Sql server 2005, поскольку для работы с ним нужны какие-то агрегаты. У меня здесь нет ничего агрегированного.

Я не уверен, что это очень эффективно, но вы можете попробовать:

 ;WITH CTE AS
(
    SELECT StationName, ScheduleNumber, ArrivalTime ScheduleTime, 'Arrival' [Arrival/Departure]
    FROM ScheduleTest 
    WHERE ArrivalTime IS NOT NULL
    UNION ALL
    SELECT StationName, ScheduleNumber, DepartureTime, 'Departure'
    FROM ScheduleTest 
    WHERE DepartureTime IS NOT NULL
)

SELECT  StationName, 
        MIN(CASE WHEN ScheduleNumber = 'ScheduleA' THEN ScheduleTime ELSE NULL END) ScheduleA,
        MIN(CASE WHEN ScheduleNumber = 'ScheduleB' THEN ScheduleTime ELSE NULL END) ScheduleB,
        [Arrival/Departure]
FROM CTE
GROUP BY StationName, [Arrival/Departure]
  

Я получил это из другого источника… Это работает, и спасибо тому, кто предложил этот ответ

ЕСЛИ OBJECT_ID( ‘tempdb..#ScheduleTest’) НЕ ЯВЛЯЕТСЯ НУЛЕВОЙ ТАБЛИЦЕЙ УДАЛЕНИЯ #ScheduleTest GO

ЕСЛИ OBJECT_ID( ‘tempdb..#tmp’) НЕ ЯВЛЯЕТСЯ НУЛЕВЫМ УДАЛЕНИЕМ ТАБЛИЦЫ #tmp GO

СОЗДАЙТЕ ТАБЛИЦУ #ScheduleTest([StationName] nvarchar NULL, [ScheduleNumber] nvarchar NULL, [ArrivalTime] nvarchar NULL, [departureTime] nvarchar NULL ) В [PRIMARY]

Вперед

—Вставить сценарии для расписания A Вставить в значения #ScheduleTest (‘Чикаго, Иллинойс, (Юнион Стейшн)’, ‘ScheduleA’, NULL, ’02:45′) Вставить в значения #ScheduleTest (‘Чикаго, Иллинойс, (центр города)’, ‘ScheduleA’, ’02:55′, ’03:00′) Вставить в значения #ScheduleTest (‘Бентон, Иллинойс, Гавань’, ‘ScheduleA’, ’08:00′, NULL) — в данном случае Бентон — конечный пункт назначения, поэтому время отправления равно нулю

— Вставить сценарии для расписания B (другой, который выполняется утром) Вставить в значения #ScheduleTest (‘Чикаго, Иллинойс, (Юнион Стейшн)’, ‘ScheduleB’, NULL, ’06:00′) Вставить в значения #ScheduleTest (‘Чикаго, Иллинойс, (центр города)’, ‘ScheduleB’, ’06:10′, ’06:15′) Вставить в #ScheduleTest значения (‘Бентон, Иллинойс, Харбор’, ‘ScheduleB’, ’11:00′, NULL)

ВЫБЕРИТЕ StationName, ScheduleNumber, ArrivalTime КАК TimeOfEvent, ‘Прибытие’ КАК [Прибытие / отправление] В #tmp ИЗ #ScheduleTest, ГДЕ ArrivalTime НЕ РАВНО НУЛЮ ОБЪЕДИНЕНИЕ ВСЕХ ВЫБЕРИТЕ StationName, ScheduleNumber, departureTime КАК TimeOfEvent, ‘Отправление’ КАК [Прибытие / отправление] Из #ScheduleTest, ГДЕ departureTime НЕ РАВНО НУЛЮ

— Из-за требования к агрегации сводки допускается только одна комбинация Расписание / станция. — Это запрос к основным данным. Теперь, чтобы изменить это: ВЫБЕРИТЕ StationName, ScheduleA, ScheduleB, [Прибытие / отправление] ИЗ #tmp КАК t PIVOT ( MAX(TimeOfEvent) ДЛЯ ScheduleNumber В ( ScheduleA, ScheduleB) ) В КАЧЕСТВЕ pvt

ОБЪЯВИТЬ @sql nVARCHAR(МАКСИМУМ), @ScheduleNumber VARCHAR(50), @PivotInList nVARCHAR(МАКСИМУМ)

УСТАНОВИТЕ @sql = ‘SELECT StationName’, УСТАНОВИТЕ @PivotInList = ‘ IN (‘

ОБЪЯВИТЕ ScheduleCursor CURSOR FAST_FORWARD Для ВЫБОРА ОТДЕЛЬНОГО номера расписания ИЗ #tmp

ОТКРОЙТЕ ScheduleCursor Или ИЗВЛЕКИТЕ NEXT ИЗ ScheduleCursor В @ScheduleNumber

ПРИ @@FETCH_STATUS = 0 НАЧНИТЕ УСТАНАВЛИВАТЬ @sql = @sql @ScheduleNumber ‘, ‘ УСТАНОВИТЕ @PivotInList = @PivotInList ‘ ‘ @ScheduleNumber ‘,’

     FETCH NEXT FROM ScheduleCursor INTO @ScheduleNumber
  

ЗАВЕРШЕНИЕ

ЗАКРЫТЬ ScheduleCursor ОСВОБОДИТЬ ScheduleCursor

УСТАНОВИТЕ @PivotInList = LEFT( @PivotInList, LEN( @PivotInList) — 1 /Удалите лишнюю запятую/) ‘)’ ВЫВЕСТИ @PivotInList

УСТАНОВИТЕ @sql = @sql ‘[Прибытие / отправление] ИЗ #tmp В КАЧЕСТВЕ t PIVOT ( MAX ( TimeOfEvent) ДЛЯ ScheduleNumber’ @PivotInList ‘) В КАЧЕСТВЕ pvt’ PRINT @sql

EXEC sp_executeSQL @sql