SQL Server count numbers

I have a table and it looks like this:

Num1 num2  num3
----------------
1     2     2<----- grouped numbers

1     2     3<----- another group 
1     2     3<----- same numbers so I have a value of 2 

2     4     4
2     2     3
2     4     3
3
3
4

I would like to know how to give groups of numbers, a number value.

Example1

1, 2, 2 are grouped horizontally

Example 2

1,2,2 this combination is shown x amount of times

1,2,2 times = 1

Example 3

1,2,3 = 2 times

2,3,3 = 4 times

This works but only on single numbers

select num, count(*)Times
from Numbers cross apply
     (values (F2), (F3), (F4),(F5),(F6),(F7),(F8)) v(num)
where num is not null
group by num
order by num;


This also works but same problem 

select value, count(*)
from Numbers
unpivot
(
  value
  for col in (F2, F3, F4,F5,F6,F7,F8,F9)
) u
group by value 
ORDER BY 1;

The idea is to expand this to 16 columns and search all rows to find the matching sets of numbers in each row. Give an output of example 3, "times" being the column name

The amount of rows=2000,columns=16

if anyone can help please post

1 answer

  • answered 2018-07-11 02:33 Gordon Linoff

    Oh, I think you want the count of each number. You can do this by unpivoting and aggregating:

    select num, count(*)
    from t cross apply
         (values (num1), (num2), (num3)) v(num)
    where num is not null
    group by num
    order by num;