SQL Server数据透视表具有多个带有日期的列 [英] SQL Server Pivot Table with multiple column with dates

问题描述

我有PIVOT的情况.

I have a PIVOT situation.

源表列:

Title   Description    Datetime                  RecordsCount
A       California     2015-07-08 10:44:39.040     5
A       California     2015-07-08 12:44:39.040     6
A       California     2015-05-08 15:44:39.040     3
B       Florida        2015-07-08 16:44:39.040     2
B       Florida        2015-05-08 19:44:39.040     4

现在我需要将此作为

                       2015-07-08     2015-05-08
Title   Description    
A       California       11              3
B       Florida           2              4

如果我们在同一日期(无论时间如何)都有两个记录计数，然后将它们相加，否则显示在不同的列中.

if we have two record counts on same dates (no matter of time) then sum them, else display in different column.

试图写这样的东西，但是会引发错误.

Trying to write something like this, but it throws errors.

<代码>从#DataQualTest中选择* PIVOT(SUM(RecordCount)FOR DateTime IN(从#DataQualTest中选择Datetime)) AS数据透视表

Select * from #DataQualTest PIVOT (SUM(RecordCount) FOR DateTime IN (Select Datetime from #DataQualTest) ) AS Pivot_Table

请帮助我解决这个问题.

Please help me out with this.

谢谢

推荐答案

不完全是逐字解决方案，但这应该可以为您提供指导.

Not exactly the word for word solution but this should give you a direction.

create table #tmp
( 
    country varchar(max)
    , date1 datetime
    , record int
)

insert into #tmp values ('California', '2010-01-01', 2)
insert into #tmp values ('California', '2010-01-01', 5)
insert into #tmp values ('California', '2012-01-01', 1)
insert into #tmp values ('Florida', '2010-01-01', 3)
insert into #tmp values ('Florida', '2010-01-01', 5)

select * from #tmp
pivot (sum(record) for date1 in ([2010-01-01], [2012-01-01])) as avg

输出

output

country     2010-01-01  2012-01-01
California  7              1
Florida     8             NULL

这篇关于SQL Server数据透视表具有多个带有日期的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！