Get query grouped by date

Transact-SQL
1

I have these table:

server ocurrences date
A      122        20200101
B      1          20200101
C      15         20200101
............

I'm tring to get these result:

			A		B		C
20200101		122		1		15

I make these query:

select server, ocurrences, date FROM NET_REPORT 
where to_char(date,'YYYYMMDD') >= '20200101' 
AND server IN ('A','B','C') GROUP BY date, server,ocurrences ORDER BY date,server;

But I can't get what I want.
Could you help me please?
Thanks

2

hi

please see following links .. hope this helps !!! :slight_smile:

One Way

Another Way

1 Like
3 
Set transaction isolation level read uncommitted
go
IF OBJECT_ID('tempdb..#t') IS NOT NULL 
    DROP TABLE #t

Create table  #t (server char(1), Ocurrences int, DDate char(8))
insert into #t values 
('A',122,'20200101'),
('B',1  ,'20200101'),
('C',15 ,'20200101')

--PIVOT  
DECLARE @Colslist VARCHAR(MAX)  

SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Server + ']'  
FROM #t  

exec (' SELECT ddate, ' + @ColsList + '
        FROM #t   
PIVOT (sum(Ocurrences) FOR [Server] IN (' + @ColsList + ')) PVT')
2 Likes