How to pivot table on multiple date rows

Question

I'm trying to pivot a SQL Server table whilst also grouping by the date column.

This is my example data:

date	ref	count
07/06/21	ref1	20
08/06/21	ref1	2
09/06/21	ref1	15
07/06/21	ref2	54
08/06/21	ref2	23

And how the result needs to be:

ref	07/06/21	08/06/21	09/06/21
ref1	20	2	15
ref2	54	23

Is it possible to do this with a SQL Server PIVOT, or does it require something else to do the grouping?

The dates are dynamic as well and based on a date range.

Pivoting is a form of aggregation, so yes, you need a `GROUP BY`. Also if those dates aren't set in stone (which I doubt) you'll need a dynamic pivot. — Thom A, Jun 07 '21 at 09:42
Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Thom A, Jun 07 '21 at 09:43
Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Thom A, Jun 07 '21 at 09:43
Also, are you really using SQL Server 2008? It's been completely unsupported now for 2 years~. — Thom A, Jun 07 '21 at 09:44
Thanks for the answers. I was unsure how to attempt it due to the dynamic date range. Turns out the dynamic pivot was indeed what I needed for this and the reason why a regular pivot was not working for me. — user3768044, Jun 07 '21 at 12:25

score 0 · Answer 1 · answered Jun 07 '21 at 09:48

0

SELECT C.REF,
SUM(
      CASE
         WHEN C.DATE='07/06/21' THEN C.COUNT ELSE 0 END
   )AS '07_06_21',
SUM(
      CASE
       WHEN C.DATE='08/06/21' THEN C.COUNT ELSE 0 END
   )AS '08_06_21',
SUM(
    CASE
       WHEN C.DATE='09/06/21' THEN C.COUNT ELSE 0 END
  )AS '09_06_21'
 FROM my_example_data AS C
 GROUP BY C.REF

You can always try this conditional aggregation-approach

answered Jun 07 '21 at 09:48

Sergey

4,719
1
6
11

Unfortunately the dates are dynamic, so this wouldn't work for me in this case, but thank you. – user3768044 Jun 07 '21 at 10:13
Then your way is "dynamic pivot" https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – Sergey Jun 07 '21 at 10:19

score 0 · Accepted Answer · answered Jun 07 '21 at 10:47

You need dynamic pivot query to do this try below query

declare @columns varchar(max)='',@sql nvarchar(max);

Create table #temp (Name nvarchar(max));

insert into #temp select distinct date from tablename

SELECT @columns+=QUOTENAME(Name) + ',' from #temp
SET @columns = LEFT(@columns, LEN(@columns) - 1);


set @sql='SELECT * FROM
(select ref,count,date from tablename) t

PIVOT(sum([count])
FOR [Dates] IN ('+@columns+')
) AS pivot_table'
EXEC (@sql)

drop table #temp

score -1 · Answer 3 · edited Jun 07 '21 at 11:20

-1

SELECT ref, [07/06/21],[08/06/21],[09/06/21] FROM
(SELECT ref, date , [count] FROM tableName) t1
PIVOT  
(  
 SUM([count]) FOR [date] IN ([07/06/21],[08/06/21],[09/06/21])) AS t2
ORDER BY t2.ref

edited Jun 07 '21 at 11:20

Dale K

25,246
15
42
71

answered Jun 07 '21 at 10:09

shinju

1

1

A good answer provides a description so the OP can learn, in addition to hopefully working code. – Dale K Jun 07 '21 at 11:20

How to pivot table on multiple date rows

3 Answers3