Hello Kristian,
I don't know if I have understood well but I would say that you want to do something like this:
declare @Demo table (App nvarchar(20), Num int)
insert into @Demo(App, Num)
values('App1',20),('App2',30),('App3',40)
select * from @Demo
pivot (sum(Num) for App in (App1,App2,App3)) as Result
If so (if not, let me know or clarify please) , you can try a simple solution regarding your own code similar to:
;with cte as
((SELECT 'AppName1' as App, COUNT(REDIRTO) AS AppCount
FROM globallogin.Eventlog
WHERE (LOGONAS = 'my@email.com') AND REDIRTO LIKE '%AppName1%'
)
UNION
(
SELECT 'AppName2' as App, COUNT(REDIRTO) AS AppCount
FROM globallogin.Eventlog
WHERE (LOGONAS = 'my@email.com') AND REDIRTO LIKE '%AppName2%'
)
UNION
(
SELECT 'AppName3' as App, COUNT(REDIRTO) AS AppCount
FROM globallogin.Eventlog
WHERE (LOGONAS = 'my@email.com') AND REDIRTO LIKE '%AppName3%'
))
select * from cte
pivot (sum(AppCount) for App in (AppName1,AppName2,AppName3)) as FinalResult
I hope this is useful for you!
Anyway I didn't like so many 'UNION' operator in the Common Table Expression, later on I'll send you a new solution more optimized.