-2

I am not exactly sure how to ask this. I have a table with the colums, timestamp,user,and another field. Looking like below:

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:

            USR1,USR2
MON,VALUE1:  2    1
MON,VALUE2:  0    1
TUE,VALUE1:  1    0

Is that possible?

I'm using MSSQL

Thank you in advance.

jdiddy886
  • 23
  • 4
  • Could you have a `USR3`, `USR4`, `USR9999999`, or are you limited to just `USR1` and `USR2`? What have you tried yourself? There are plenty of questions about (dynamic) pivots on SO, have you looked at their answers? – Thom A Jul 06 '18 at 10:23
  • 3
    [so] is *not* a free code writing service. You are expected to try to **write the code yourself**. After [doing more research](http://meta.stackoverflow.com/questions/261592) if you have a problem you can **post what you've tried** with a **clear explanation of what isn't working** and providing a **[mcve]**. I suggest reading [ask] a good question and [the perfect question](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/). Also, be sure to take the [tour]. – Igor Jul 06 '18 at 10:23
  • Try doing a little research into GROUP BY, since that should allow you to select values collected by certain parameters, in this case, the day of the week and the value there. Then order by the day, then by the value, and output the results in a table. – DingusKhan Jul 06 '18 at 10:25
  • @igor, I am mostly interested in wether it is possible using a select statement, I've tryied multiple types of joins, but didn't even come close. – jdiddy886 Jul 06 '18 at 10:25
  • @jdiddy886 - then post your attempt(s) and why it isn't working. – Igor Jul 06 '18 at 10:26
  • @jdiddy886 concatenate Time with Field, then use a dynamic pivot with `COUNT()` function. – EzLo Jul 06 '18 at 10:26
  • @DinusKhan, I've already tried that with no luck, but that won't give me the formating i want. – jdiddy886 Jul 06 '18 at 10:27
  • @Larnu as already stated. I could have a USR66 or a user called Larnu. Also, i have no idea what a dynamic pivot is, I'm gonna look it up – jdiddy886 Jul 06 '18 at 10:28
  • @igor My attempt includes trying to make some sort of join between a SELECT DISTINCT on user,and another table where i do a count on user and groups by user,time,field. But I need the users as columns, so it is not really working. Right now i'm trying to grasp wether i can use pivot – jdiddy886 Jul 06 '18 at 10:43

1 Answers1

-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
Gaj
  • 888
  • 5
  • 5
  • The problem with that approach is that you would have to know the usernames before hand, which i don't. Obviously i could could use SELECT DISTINCT on the users (Which is part of the solution i already tried), but that combined with grouping by Time and Field and using COUNT on user is as far as i got – jdiddy886 Jul 06 '18 at 10:39