英文:
Query to split data from columns into multiple rows
问题
我有一个表格，其中包含交付和提取的真/假列在同一行。我想查询这些数据，以便它返回分开的交付和提取的行。
当前表格：
| 订单 | 名称 | Delvr | 交付日期 | 提取 | 提取日期 | 数量 | 商品名称 |
|---|---|---|---|---|---|---|---|
| 198747 | 乔·狄特 | 1 | 7/31/2023 | 1 | 8/4/2023 | 32 | 商品1 |
| 198747 | 乔·狄特 | 1 | 7/31/2023 | 1 | 8/4/2023 | 20 | 商品2 |
| 198747 | 乔·狄特 | 1 | 7/31/2023 | 1 | 8/4/2023 | 5 | 商品3 |
预期查询结果：
| 订单 | 名称 | 行程类型 | 行程日期 | 数量 | 商品名称 |
|---|---|---|---|---|---|
| 198747 | 乔·狄特 | 交付 | 7/31/2023 | 32 | 商品1 |
| 198747 | 乔·狄特 | 交付 | 7/31/2023 | 20 | 商品2 |
| 198747 | 乔·狄特 | 交付 | 7/31/2023 | 5 | 商品3 |
| 198747 | 乔·狄特 | 提取 | 8/4/2023 | 32 | 商品1 |
| 198747 | 乔·狄特 | 提取 | 8/4/2023 | 20 | 商品2 |
| 198747 | 乔·狄特 | 提取 | 8/4/2023 | 5 | 商品3 |
需要一个名为"行程类型"的新列，对于每一行中标记为"Delvr"或"提取"的真值，都将创建一个新行，其"行程类型"为"交付"或"提取"。
如果行是"交付"（或提取）的"行程类型"，则"交付日期"（或"提取日期"）将在名为"行程日期"的新列中返回。
英文:
I have a table that has true/false columns for delivery and pickup in the same row. I would like to query this data so that it returns delivery and pickup in separate rows.
Current Table:
| Order | NAME | Delvr | DeliveryDate | Pickup | PickupDate | QTY | Item Name |
|---|---|---|---|---|---|---|---|
| 198747 | Joe Dirt | 1 | 7/31/2023 | 1 | 8/4/2023 | 32 | Item 1 |
| 198747 | Joe Dirt | 1 | 7/31/2023 | 1 | 8/4/2023 | 20 | Item 2 |
| 198747 | Joe Dirt | 1 | 7/31/2023 | 1 | 8/4/2023 | 5 | Item 3 |
Expected Query Result:
| Order | NAME | TripType | TripDate | QTY | Item Name |
|---|---|---|---|---|---|
| 198747 | Joe Dirt | Delivery | 7/31/2023 | 32 | Item 1 |
| 198747 | Joe Dirt | Delivery | 7/31/2023 | 20 | Item 2 |
| 198747 | Joe Dirt | Delivery | 7/31/2023 | 5 | Item 3 |
| 198747 | Joe Dirt | Pickup | 8/4/2023 | 32 | Item 1 |
| 198747 | Joe Dirt | Pickup | 8/4/2023 | 20 | Item 2 |
| 198747 | Joe Dirt | Pickup | 8/4/2023 | 5 | Item 3 |
A new column would be needed called "TripType" and for every row marked true for "Delvr" or "Pickup" a new row would be made with the "TripType" of "Delivery" or "Pickup"
If the row was for a "TripType" delivery (or pickup), the "DeliveryDate" (or "PickupDate) would return in a new column called "TripDate".
答案1
得分: 1
以下是翻译好的部分：
一个相对简单的解决方案是将两个单独的查询合并在一起 - 一个用于送货，一个用于取货
SELECT [Order], [NAME], 'Delivery' AS TripType,
DeliveryDate AS TripDate, QTY, [Item Name]
FROM [table]
WHERE Delvr = 1
UNION ALL
SELECT [Order], [NAME], 'Pickup' AS TripType,
Pickup AS TripDate, QTY, [Item Name]
FROM [table]
WHERE Pickup = 1
英文:
A relatively simple solution is to UNION two separate queries - one for the delivery, one for the pickup
SELECT [Order], [NAME], 'Delivery' AS TripType,
DeliveryDate AS TripDate, QTY, [Item Name]
FROM [table]
WHERE Delvr = 1
UNION ALL
SELECT [Order], [NAME], 'Pickup' AS TripType,
Pickup AS TripDate, QTY, [Item Name]
FROM [table]
WHERE Pickup = 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论