SQL Pivot 基于两列之间的值

Question

我有一个表格，其中有两列，一列用于开始日期，另一列用于结束日期。我需要编写一个查询，该查询将为一年中的每个月返回一列，如果月份在 0 之间，则该列的值为 1，否则。 PIVOT 语句似乎是我在这里寻找的，但据我所知，PIVOT 子句正在寻找匹配值，而不是检查值是否介于其他两个之间。 PIVOT 子句在这里是正确的构造，还是我需要分解并编写 12 个 case 语句然后聚合它们？

Answer 1

我想我已经找到了解决方案。有 3 个基本步骤:

1. 获取 12 个月中每个月的日期
2. 检查此日期是否在开始日期和结束日期之间
3. 透视结果

为了获得 12 个日期，每个月一个，我使用了一些类似于 WITH 语句的递归来创建一个包含 1 列 12 个日期的临时表:

WITH months (date) AS (
SELECT GETDATE() AS date
UNION ALL
SELECT 
    DATEADD(MONTH,1,date)
FROM months
WHERE DATEDIFF(MONTH,GETDATE(),date) < 12)

从这里我可以将这个临时表与包含我真正关心的信息的表交叉连接。然后我使用 WHERE date BETWEEN start AND end 来过滤掉不属于那个月的任何条目。所以像这样:

SELECT other.Title, MONTH(months.date) CROSS JOIN other
WHERE months.date BETWEEN other.start AND other.end

在这一步中，我们必须小心只选择我们在结果中明确需要的列，或者将使用 PIVOT 语句聚合的列。

最后，我们必须调整结果:

PIVOT (MAX(PID) FOR date IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))

因此，结果查询可能类似于:

WITH months (date) AS (
SELECT GETDATE() AS date
UNION ALL
SELECT 
DATEADD(MONTH,1,date)
FROM months
WHERE DATEDIFF(MONTH,GETDATE(),date) < 12)
SELECT  Title,
    [1]     AS January,
    [2]     AS February,
    [3]     AS March,
    [4]     AS April,
    [5]     AS May,
    [6]     AS June,
    [7]     AS July,
    [8]     AS August,
    [9]     AS September,
    [10]    AS October,
    [11]    AS November,
    [12]    AS December
FROM
(
SELECT other.Title,MONTH(months.date)
CROSS JOIN other
WHERE months.date BETWEEN other.startDate AND other.endDate
) AS subquery
PIVOT (MAX(PID) FOR date IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS p

我已经去掉了所有其他的复杂性，加入我需要的其他信息，所以这实际上不是我写的查询，但它应该封装我用来获取结果的基本查询结构因为我需要它。