SQL Pivot基于两列之间的值

I have a table in which I have, among other things, two columns, one for start date and another for end date. I need to write a query that will return a column for each month of the year and the value of that column is 1 if the month is between 0 otherwise. The PIVOT statement seems to be what I am looking for here, but from the best I can tell the PIVOT clause is looking to match values, not check if value is between two others. Is the PIVOT clause the right construct here, or do I need to break down and write 12 case statements and then aggregate those?

我有一张表,其中包括两列,一列用于开始日期,另一列用于结束日期。我需要编写一个查询,该查询将返回一年中每个月的列,如果月份在0之间,则该列的值为1。 PIVOT语句似乎是我在这里寻找的,但从最好的我可以告诉PIVOT子句寻找匹配值,而不是检查值是否在两个其他之间。 PIVOT子句在这里是正确的构造,还是我需要分解并编写12个案例语句然后汇总它们?

3 个解决方案

#1

I think I've got the solution here. There are 3 basic steps:

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

Get the a date in each of the 12 months

获取12个月中的每一个的日期

Check if this date is between the start and end date

检查此日期是否在开始日期和结束日期之间

PIVOT the result

PIVOT结果

To get the 12 dates, one for each month, I used a little recursive like WITH statement to create a temporary table with 1 column of 12 dates:

为了获得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)

From here I can CROSS JOIN this temporary table with the table with the information I really care about. Then I use WHERE date BETWEEN start AND end to filter off any entries which don't belong to that month. So something like this:

从这里我可以CROSS JOIN这个临时表与表格,我真正关心的信息。然后我使用WHERE日期BETWEEN开始和结束来过滤掉那些不属于那个月的条目。所以像这样:

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

In this step we must be careful to only SELECT the columns we explicity desire in the result, or those which will be aggregated using the PIVOT statement.

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

Lastly, we must pivot the result:

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

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

So the resultant query might look something like:

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

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

I've stripped out all of the other complexity I went through to join in other information I needed, so this isn't actually the query I wrote, but it should encapsulate the basic query structure that I used to get the result as I needed it.

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

#2

I'd go with the 12 case statements.

我将使用12个案例陈述。

That's actually the solution I sketched out before I went back and saw you mentioned in it your question.

这实际上是我在回去之前勾画出来的解决方案,看到你在其中提到了你的问题。

#3

Why not compute a column for the month and then use it to pivot?

为什么不计算一个月的列然后用它来转动呢?

datepart(month,[date])

or did I misunderstand the problem?

还是我误解了这个问题?

#1