question

DipeshPuriGoswami-1109 avatar image
0 Votes"
DipeshPuriGoswami-1109 asked EchoLiu-msft commented

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

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you have any update?Did the following methods solve your problem?If not, please provide more details.If it is resolved,please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered

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!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @DipeshPuriGoswami-1109

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.