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

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Jul 8, 2015 at 18:35

Amar · Accepted Answer · 2015-07-08 18:56:45Z

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

answered Jul 8, 2015 at 18:56

Amar

3031 gold badge3 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5094620 Over a year ago

Hello Thanks for your response, issue is with timestamp, when I add time stamp to the data, it returns nulls for record counts else its working fine. there are several counts during single date that's the issue. By Any chance can we add the time stamp?

Abecee · Accepted Answer · 2015-07-10 22:53:08Z

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.

Collectives™ on Stack Overflow

SQL Server Pivot Table with multiple column with dates

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related