SQL - Pivot data with date in the columns (Azure SQL)

Question

Hello,

I have below problem statement -
A table contains incremental data as below -

Name | Date | Time(in min)
A | 2021-05-26 20:39:15.3366667 | 2
B | 2021-05-26 17:32:15.3366667 | 1
C | 2021-05-26 21:37:15.3366667 | 4
D | 2021-05-26 15:38:11.3366667 | 10
A | 2021-05-26 20:29:15.3366667 | 12
B | 2021-05-26 17:35:15.3366667 | 22
C | 2021-05-26 21:17:15.3366667 | 12
D | 2021-05-26 15:31:11.3366667 | 19
A | 2021-05-25 20:36:15.3366667 | 4
B | 2021-05-25 16:34:15.3366667 | 6
C | 2021-05-25 23:32:15.3366667 | 5
D | 2021-05-25 22:59:15.3366667 | 11
.
.
.
and so on.

I want this data in below format -
Name | 2021-05-26 | 2021-05-25 | 2021-05-24 | 2021-05-23 | 2021-05-22
A | 14 | 4 | ....
B | 23 | 6 | ....
C | 16 | 5 | ....
D | 29 | 11 | ....

The column names in the desired table will keep on changing and need to be that of rolling 5 days. In the value field, I need the sum of time(in min) for the given combination of date and Name. For eg. - For A and for 2021-05-26, it is 2 + 12 = 14...and so on.

We can use pivot here, but I am facing difficulty in the same. Also, I do not have permission to create any table. So options with CTE would be preferred.

Thank you.

Dipesh

Answer 1

What you need for your problem is the dynamic PIVOT, which is relatively easy to write. First you do

declare @Columns nvarchar(max), @SQL nvarchar(max);

;with cteDates as (select DISTINCT TOP (5) CAST([excluded_dttm] AS DATE) AS [date], 
QUOTENAME(convert(varchar(10), excluded_dttm, 120)) as [colDate] from dbo.zz_test T order by [date] DESC)

SELECT @Columns = STUFF((SELECT ', ' + colDate FROM cteDates ORDER BY ', ' + colDate DESC FOR XML PATH('')),1,2,'');
PRINT @Columns;

And then generate the whole PIVOT query dynamically using the above. There are lots of samples of dynamic pivot just in this forum alone.

Good luck!

Answer 2

You can always create a temp table. You cannot do this in a single query, a as query returns fixed column names. So it is either dynamic SQL, or the technique below where I insert into a temp table and then rename the columns.

DROP TABLE IF EXISTS #temp
CREATE TABLE #temp (Name char(1) NOT NULL PRIMARY KEY,
                    Date0 int NULL,
                    Date1 int NULL,
                    Date2 int NULL,
                    Date3 int NULL,
                    Date4 int NULL)

DECLARE @date0 date = convert(date, sysdatetime()),
        @date1 date = convert(date, dateadd(DAY, -1, sysdatetime())),
        @date2 date = convert(date, dateadd(DAY, -2, sysdatetime())),
        @date3 date = convert(date, dateadd(DAY, -3, sysdatetime())),
        @date4 date = convert(date, dateadd(DAY, -4, sysdatetime()))

INSERT #temp (Name, Date0, Date1, Date2, Date3, Date4)
   SELECT Name, SUM(CASE convert(date, Date) WHEN @date0 THEN [Time(in min)] END),
                SUM(CASE convert(date, Date) WHEN @date1 THEN [Time(in min)] END),
                SUM(CASE convert(date, Date) WHEN @date2 THEN [Time(in min)] END),
                SUM(CASE convert(date, Date) WHEN @date3 THEN [Time(in min)] END),
                SUM(CASE convert(date, Date) WHEN @date4 THEN [Time(in min)] END)
  FROM  tbl

EXEC tempdb..sp_rename '#temp.Date0', @date0, 'COLUMN'
EXEC tempdb..sp_rename '#temp.Date1', @date1, 'COLUMN'
EXEC tempdb..sp_rename '#temp.Date2', @date2, 'COLUMN'
EXEC tempdb..sp_rename '#temp.Date3', @date3, 'COLUMN'
EXEC tempdb..sp_rename '#temp.Date4', @date4, 'COLUMN'

SELECT * FROM #temp

Answer 3

Hi @Dipesh Puri Goswami ，

Please also try:

CREATE TABLE #test([Name] char(5),[Date] datetime2,[Time(in min)]  int)  
INSERT INTO #test VALUES('A','2021-05-26 20:39:15.3366667',2)  
                       ,('B','2021-05-26 17:32:15.3366667',1)  
					   ,('C','2021-05-26 21:37:15.3366667', 4)  
					   ,('D','2021-05-26 15:38:11.3366667',10)  
					   ,('A','2021-05-26 20:29:15.3366667',12)  
					   ,('B','2021-05-26 17:35:15.3366667',22)  
					   ,('C','2021-05-26 21:17:15.3366667',12)  
					   ,('D','2021-05-26 15:31:11.3366667',19)  
					   ,('A','2021-05-25 20:36:15.3366667', 4)  
					   ,('B','2021-05-25 16:34:15.3366667',6)  
					   ,('C','2021-05-25 23:32:15.3366667',5)  
					   ,('D','2021-05-25 22:59:15.3366667',11)  
					   ,('A','2021-05-24 20:36:15.3366667', 2)  
					   ,('B','2021-05-24 16:34:15.3366667',16)  
					   ,('C','2021-05-24 23:32:15.3366667',10)  
					   ,('D','2021-05-24 22:59:15.3366667',1)  
					   ,('A','2021-05-23 20:36:15.3366667', 3)  
					   ,('B','2021-05-23 16:34:15.3366667',9)  
					   ,('C','2021-05-23 23:32:15.3366667',9)  
					   ,('D','2021-05-23 22:59:15.3366667',13)  
					   ,('A','2021-05-22 20:36:15.3366667', 5)  
					   ,('B','2021-05-22 16:34:15.3366667',6)  
					   ,('C','2021-05-22 23:32:15.3366667',7)  
					   ,('D','2021-05-22 22:59:15.3366667',13)  
                       ,('A','2021-05-22 20:36:15.3366667', 5)  
					   ,('B','2021-05-22 16:34:15.3366667',6)  
					   ,('C','2021-05-22 23:32:15.3366667',7)  
					   ,('D','2021-05-22 22:59:15.3366667',13)  
  
SELECT * FROM #test  
  
--Static sql  
;WITH cte  
as(SELECT Name,CAST([Date] as DATE) [Date],SUM([Time(in min)] ) Val  
FROM #test  
GROUP BY Name,CAST([Date] as DATE))  
  
SELECT * FROM (SELECT [Name],[date],val FROM cte) as t    
pivot (MAX(val) FOR [date] in ([2021-05-26],  
[2021-05-25],[2021-05-24],[2021-05-23],[2021-05-22])) as p  
  
  
--Dynamic sql  
DECLARE @sql nvarchar(max)  
DECLARE @s nvarchar(max)  
  
SELECT @s=STUFF(( SELECT DISTINCT ',['+CAST(CAST([Date] as date) as nvarchar)+']'  FROM #test FOR XML PATH('') ), 1, 1, '')  
  
SET @sql=N';WITH cte  
as(SELECT Name,CAST([Date] as DATE) [Date],SUM([Time(in min)] ) Val  
FROM #test  
GROUP BY Name,CAST([Date] as DATE))  
SELECT * FROM (SELECT [Name],[Date],Val FROM cte) as t    
pivot (MAX(val) FOR [date] in ('+@s+')) as p'  
  
EXECUTE sp_executesql  @sql  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.