SQL Server Pivot Table with multiple column with d

问题:

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

回答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

回答2:

If you want to be more flexible, you need some pre-processing to get from full timestamps to days (in order for later on the PIVOT's grouping to actually have the anticipated effect):

CREATE VIEW DataQualTestView AS
  SELECT
    title
    , description
    , DATEFROMPARTS (DATEPART(yyyy, date_time),
                     DATEPART(mm, date_time),
                     DATEPART(dd, date_time)) AS day_from_date_time
    , recordsCount
  FROM DataQualTest
;

From there you could continue:

DECLARE @query AS NVARCHAR(MAX)
DECLARE @columns AS NVARCHAR(MAX)

SELECT @columns = ISNULL(@columns + ',' , '') 
                  + QUOTENAME(day_from_date_time)
FROM (SELECT DISTINCT
        day_from_date_time
      FROM DataQualTestView) AS TheDays

SET @query = 
  N'SELECT
    title
    , description
    , ' + @columns + '
  FROM DataQualTestView
  PIVOT(SUM(recordsCount) 
        FOR day_from_date_time IN (' + @columns + ')) AS Pivoted'

EXEC SP_EXECUTESQL @query

GO

... and would get:

| title | description | 2015-05-08 | 2015-07-08 |
|-------|-------------|------------|------------|
|     A |  California |          3 |         11 |
|     B |     Florida |          4 |          2 |

See it in action: SQL Fiddle.

Please comment, if and as this requires adjustment / further detail.