基于两列之间的值进行SQL透视表

Question

我有一个表格，在其中有两列，一列为开始日期，另一列为结束日期。我需要编写一个查询，返回每年的每个月份以及该列的值是1，如果月份在开始和结束日期之间，否则为0。似乎PIVOT语句是我在这里寻找的，但从我最好的理解来看，PIVOT子句正在寻找匹配的值，而不是检查值是否介于两个值之间。PIVOT子句是正确的构造吗？还是我需要拆分并编写12个case语句，然后汇总它们？

- Bryan Ward

Answer 1

我认为我已经找到了解决方案。有三个基本步骤：

1. 获取每个月的日期
2. 检查这个日期是否在开始和结束日期之间
3. 对结果进行PIVOT操作

为了获取12个日期，即每个月的一个日期，我使用了递归WITH语句创建了一个临时表，其中包含12个日期的1列：

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

我可以使用临时表与我真正关心的信息表进行 CROSS JOIN。然后使用 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

我已经剥离了所有其他复杂性，以便加入我需要的其他信息，所以这实际上不是我编写的查询，但它应该概括了我用来获得所需结果的基本查询结构。

- Bryan Ward

Answer 2

为什么不计算一个月的列，然后使用它进行旋转？

datepart(month,[date])

还是我误解了问题？

- WGroleau

Answer 3

我会选择12个case语句。

实际上，在我回去看到你在问题中提到它之前，我就已经勾画出了这个解决方案。

- Eric H.