获取SQL查询结果horisontal [英] Get SQL query result horisontal
问题描述
大家好,
我需要帮忙解决我的SQL查询结果问题。
我正在计算从我的登录系统到不同应用程序的重定向,我的所有计数都是垂直分组的。如何根据每个应用程序对结果进行水平分组?
我想要这样的结果
App1:App2:App3
4:7:8
我得到这个
App1
4
7
8
Im on SQL server 2014
我尝试过:
(SELECT COUNT(REDIRTO)AS App1
来自globallogin.Eventlog
WHERE(LOGONAS ='my@email.com')和REDIRTO LIKE'%AppName1%'
)
UNION
(
SELECT COUNT(REDIRTO)AS App2
来自globallogin.Eventlog
WHERE( LOGONAS ='my@email.com')和REDIRTO LIKE'%AppName2%'
)
UNION
(
SELECT COUNT(REDIRTO)AS App3
来自globallogin.Eventlog
WHERE(LOGONAS ='my@email.com')和REDIRTO LIKE %AppName3%'
)
Hello Kristian,
I不知道我是否理解得很好,但我会说你想做这样的事情:
声明 @ Demo table (App nvarchar ( 20 ),Num int )
插入 进入 @ Demo (App ,Num)
值(' App1' , 20 ),(' App2' , 30 ),(' App3' , 40 )
选择 * 来自 @ Demo
pivot(sum(Num) for App in (App1,App2,App3)) as 结果
如果是这样(如果没有,请告诉我或澄清一下),您可以尝试一个关于您自己的代码的简单解决方案,类似于:
; cte as
(( SELECT ' AppName1' as App,COUNT(REDIRTO) AS AppCount
FROM globallogin。事件日志
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 ' < span class =code-stri ng> AppName3' as App,COUNT(REDIRTO) AS AppCount
FROM globallogin.Eventlog
WHERE (LOGONAS = ' my@email.com') AND REDIRTO LIKE ' %AppName3%'
))
选择 * 来自 cte
pivot(sum(AppCount) for App in (AppName1,AppName2,AppName3)) as FinalResult
我希望这对你有用!
无论如何我不喜欢在Common Table Expression中有这么多'UNION'操作符,稍后我会给你一个更优化的新解决方案。
另一种解决方案可能是:
; with cte as
(( SELECT ( case 当 REDIRTO 喜欢 ' %AppName1%' 然后 ' AppName1'
当 REDIRTO 喜欢 ' %AppName2%' 然后 ' AppName2'
何时 REDIRTO 喜欢 ' %AppName3% ' 然后 ' AppName3'
end ) as App,
COUNT(*) AS AppCount
FROM globallogin.Eventlog
WHERE (LOGONAS = ' my@email.com') AND REDIRTO LIKE ' %AppName [123]%' / * 注意这里的正则表达式* /
< span class =code-keyword> group by ( case 何时 REDIRTO ' %AppName1%' 然后 ' AppName1'
当 REDIRTO 喜欢 ' %AppName2%' 然后 ' AppName2'
REDIRTO 喜欢 ' %AppName3%' 然后 ' AppName3'
结束)
)
)
选择 * 来自 cte
pivot(sum(AppCount) for App in (AppNa me1,AppName2,AppName3)) as FinalResult
Hello everyone,
I need helt to UNPIVOT my SQL Query result.
I am counting redirects from my login system to different applications and all my counts are grouped vertically. How do i get the result horisontally grouped by each application?
I Want a result like this
App1: App2: App3
4: 7: 8
Im getting this
App1
4
7
8
Im on SQL server 2014
What I have tried:
(SELECT COUNT(REDIRTO) AS App1
FROM globallogin.Eventlog
WHERE (LOGONAS = 'my@email.com') AND REDIRTO LIKE '%AppName1%'
)
UNION
(
SELECT COUNT(REDIRTO) AS App2
FROM globallogin.Eventlog
WHERE (LOGONAS = 'my@email.com') AND REDIRTO LIKE '%AppName2%'
)
UNION
(
SELECT COUNT(REDIRTO) AS App3
FROM globallogin.Eventlog
WHERE (LOGONAS = 'my@email.com') AND REDIRTO LIKE '%AppName3%'
)解决方案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.
Another solution could be:
;with cte as ((SELECT (case when REDIRTO like '%AppName1%' then 'AppName1' when REDIRTO like '%AppName2%' then 'AppName2' when REDIRTO like '%AppName3%' then 'AppName3' end) as App, COUNT(*) AS AppCount FROM globallogin.Eventlog WHERE (LOGONAS = 'my@email.com') AND REDIRTO LIKE '%AppName[123]%' /* Note regular expression here*/ group by (case when REDIRTO like '%AppName1%' then 'AppName1' when REDIRTO like '%AppName2%' then 'AppName2' when REDIRTO like '%AppName3%' then 'AppName3' end) ) ) select * from cte pivot (sum(AppCount) for App in (AppName1,AppName2,AppName3)) as FinalResult
这篇关于获取SQL查询结果horisontal的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!