Sql-Server
如何根據列中的值動態生成行
我正在嘗試編寫一個查詢來非規範化一些數據以集成到我們的數據倉庫,特別是圍繞生成一些時隙。
在我的源系統中,我有一個表,它有一個開始時間和結束時間,然後是一個指定每 15 分鐘間隔有多少預約空檔可用的列。
例子:
Location | Start Time | End Time | Appointment Slots A | 9:00 am | 5:00 pm | 2 B | 9:00 am | 5:00 pm | 1
我已經能夠使用 CTE 生成 15 分鐘塊的列表。但是,每個 15 分鐘的插槽只是說位置 A 有 2 個插槽,位置 B 有 1 個插槽。我想要的是能夠為位置 A 每 15 分鐘生成 2 行,每 15 分鐘生成 1 行對於位置 B。
這是一個“數字”表變得非常方便的例子!如果您不熟悉這個概念,請閱讀 Adam Machanic 的這篇博文:您需要一個數字表
numbers 表用於連接到 Appointment Slots 列(在本例中)以複製每一行AppointmentSlots次數。
-- A simple recreation of your CTE data... CREATE TABLE #AppointmentPeriods ( AppointmentLocation CHAR(1), AppointmentStartTime TIME, AppointmentEndTime TIME, AppointmentSlotsAvailable SMALLINT ) INSERT INTO #AppointmentPeriods VALUES ('A', '09:00', '09:15', 2), ('A', '09:15', '09:30', 2), ('A', '09:30', '09:45', 2), -- .... ('A', '16:30', '16:45', 2), ('A', '16:45', '17:00', 2), ('B', '09:00', '09:15', 1), ('B', '09:15', '09:30', 1), ('B', '09:30', '09:45', 1), -- .... ('B', '16:30', '16:45', 1), ('B', '16:45', '17:00', 1) -- Numbers table CREATE TABLE #Numbers (NumberValue SMALLINT) INSERT INTO #Numbers -- TOP value should be changed so it is greater than the -- maximum number of potential appointment slots any location can have SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects SELECT #AppointmentPeriods.*, NumberValue AS AppointmentSlotNumber FROM #AppointmentPeriods INNER JOIN #Numbers ON AppointmentSlotsAvailable >= NumberValue
查詢輸出:
Location | StartTime | EndTime | AppointmentSlots | SlotNumber A | 09:00 | 09:15 | 2 | 1 A | 09:00 | 09:15 | 2 | 2 A | 09:15 | 09:30 | 2 | 1 A | 09:15 | 09:30 | 2 | 2 B | 16:30 | 16:45 | 1 | 1 B | 16:45 | 17:00 | 1 | 1
PS:生成數字表的方法有很多:SO:創建和填充數字表的最佳方法是什麼?