This question already has an answer here:
这个问题在这里已有答案:
- SQL Server dynamic PIVOT query? 6 answers
SQL Server动态PIVOT查询? 6个答案
I am not exactly sure how to ask this. I have a table with the colums, timestamp,user,and another field. Looking like below:
我不确定如何问这个问题。我有一个包含colums,timestamp,user和另一个字段的表。如下所示:
USER,TIME,FIELD USR1,MON,VALUE1 USR1,TUE,VALUE1 USR2,MON,VALUE1 USR2,MON,VALUE2 USR1,MON,VALUE1
There is no upper level to the amount of users, they can be added on the fly and there is no naming convention. What i want to do is make a select statement, and get something like be this:
用户数量没有上限,可以动态添加,也没有命名约定。我想做的是做一个select语句,得到像这样的东西:
USR1,USR2
MON,VALUE1: 2 1
MON,VALUE2: 0 1
TUE,VALUE1: 1 0
Is that possible?
那可能吗?
I'm using MSSQL
我正在使用MSSQL
Thank you in advance.
先感谢您。
1 个解决方案
#1
-1
Try this
select time, FIELD, sum(case when usr = 'USR1' then 1 else 0 end) as usr1,
sum(case when usr = 'USR2' then 1 else 0 end) as usr2
from tst
group by time, FIELD
order by time
#1
-1
Try this
select time, FIELD, sum(case when usr = 'USR1' then 1 else 0 end) as usr1,
sum(case when usr = 'USR2' then 1 else 0 end) as usr2
from tst
group by time, FIELD
order by time