SQL Server Pivot Table with multiple column with dates

Question

I have a PIVOT situation.

Source table columns:

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

Now I need this pivoted as

                       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.

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

Please help me out with this.

Thanks

Answer 1

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