0

I have the following table:

IdSce Year NoIte Value
1     0    1     1 
1     0    2     5
1     0    3     1
1     1    1     2 
1     1    2     3
1     1    3     2
2     0    1     4     
2     0    2     4
2     0    3     1
2     1    1     2 
2     1    2     4
2     1    3     3

I want to group by IdSce and Year, and show each possible value and count how many time each value appears like this:

IdSce Year Value1  Value2  Value3  Value4  Value5
1     0    2       0       0       0       1 
1     1    0       2       1       0       0
2     0    1       0       0       2       0
2     1    0       1       1       1       0

Thanks !

EDIT

shawnt00 is really close to what I want, but I'm looking to do it as dynamic as possible, meaning if I have 10 different values for the column value, I will be missing information in my table. Therefore, if I have 10 different values, I want 10 new columns (value1, value2, ... , value10)

This is what I've tried so far:

SELECT  IdSce 
        ,Year
        ,SUM(CASE WHEN Value >= 0 and Value < 1 THEN 1 else 0 end) Zero
        ,SUM(CASE WHEN Value >= 1 and Value < 2 THEN 1 else 0 end) One
        ,SUM(CASE WHEN Value >= 2 and Value < 3 THEN 1 else 0 end) Two   
        ,SUM(CASE WHEN Value >= 3 and Value < 4 THEN 1 else 0 end) Three
        ,SUM(CASE WHEN Value >= 4 and Value < 5 THEN 1 else 0 end) Four
        ,SUM(CASE WHEN Value >= 5 THEN 1 else 0 end) FiveMore
        ,SUM(CASE WHEN Value >= 0 THEN 1 else 0 end) Total  
FROM    Table
GROUP BY    IdSce
            ,Year

Thanks for the help again!

RegularNormalDayGuy
  • 685
  • 1
  • 8
  • 25
  • 4
    Solved many times on here. Either use `pivot` or `count(case when "Value" = 1 then 1 end) as "1", ...` – shawnt00 Nov 07 '16 at 18:38
  • @shawnt00 .. you should post it as an answer. – Vamsi Prabhala Nov 07 '16 at 18:39
  • Why do you need to transform the data to be across in SQL? Can't the UI do that for you? If so a simple aggregate would work! If you have to do it dynamically then you have to use DYNAMIC SQL. Example: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – xQbert Nov 07 '16 at 18:52
  • @xQbert Nope, it has to be exported to Excel after, and if I don't find a way to diminish the number of rows, I'm going to have millions of rows since Year and Iteration are usually from 0 to 99 – RegularNormalDayGuy Nov 07 '16 at 18:54
  • So... export to excel and then pivot in excel... ok if i'ts really millions of rows maybe excel can't handle the volume so I can see why you may have to pivot before excel... but I don't know many people who look at millions of rows in a file either... – xQbert Nov 07 '16 at 18:55
  • You could certainly preaggregate the values prior to pivoting. That should reduce the number of rows substantially if that's a major hurdle in your approach. At the same time there are column limits in Excel too. – shawnt00 Nov 07 '16 at 18:56
  • 1
    @shawnt00 That could work, I group by IdSce, Year and Value, and add a new column Count(*) to count how many times each value occurs. That way, I reduce the number of rows and can finish the analysis in Excel. – RegularNormalDayGuy Nov 07 '16 at 19:04

1 Answers1

3

Ok, I'll do it!

select IdSce, "Year"
    count(case when Value = 1 then 1 end) as "1",
    count(case when Value = 2 then 1 end) as "2",
    count(case when Value = 3 then 1 end) as "3",
    count(case when Value = 4 then 1 end) as "4",
    count(case when Value = 5 then 1 end) as "5"
from T
group by IdSce, "Year"

I think you'll often find this filed under "conditional aggregation". SQL Server has a proprietary syntax that uses pivot if you want to look into that also.

shawnt00
  • 16,443
  • 3
  • 17
  • 22
  • Yes, thanks for answering, I tried that, but thing is I want to do it as dynamic as possible. I want it to show ALL values possible and count how many there are, so if there are 10 different values, I want 10 new columns. I'll add the edit. – RegularNormalDayGuy Nov 07 '16 at 18:47
  • Dynamic pivot generally involves dynamic sql unless you use other tools. You'll find solutions to that problem as well with those keywords. – shawnt00 Nov 07 '16 at 18:54