Click here to Skip to main content
14,662,171 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi!

I have a table like this:

nr1	nr2	nr3	nr4	nr5	nr6	nr7	nr8	nr9	nr10
1	4	5	6	8	9	10	17	18	19
3	4	5	7	10	11	14	15	17	18
1	3	5	9	10	12	13	15	16	18
2	3	4	7	8	11	15	16	17	19
5	6	8	9	10	11	13	14	16	19
2	4	8	10	11	12	13	14	19	20
2	5	7	8	10	14	15	16	18	19
1	5	7	8	9	12	13	14	15	18
1	2	3	4	7	10	11	12	14	17
1	3	5	6	7	10	13	14	17	20


I would like to achieve the following:
1) Put all those numbers in a #TEMP table containing just one column
2) Calculate number of occurrences of all numbers.

What I have tried:

Looks like I found this solution that looks working for me:

SELECT x.AllNumbers, COUNT(*) AS Reps
	FROM NumbersTable
	CROSS APPLY (SELECT AllNumbers FROM (VALUES (nr1), (nr2), (nr3), (nr4), (nr5), (nr6), (nr7), (nr8), (nr9), (nr10)) AS t (AllNumbers)) x
	GROUP BY AllNumbers
	ORDER BY Reps DESC
Posted
Updated 18-Sep-20 23:01pm
v2
Comments
Jörgen Andersson 21-Sep-20 2:29am
   
The 'official' way of doing it is using unpivot, but if you don't care which column your value came from, the method you found should do just fine.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100