sql server: Creating a ranked list by assigning weights

Question

I have a table Survey with 10 entries. http://i.imgur.com/9pvqWEW.png. 10 users had to submit a ranked list of items 1-5. 1 being most important. How can I write a query that can assign a weight to each column, count the number of times a item occurred in that column, and return a the 5 items in a ranked list 1-5 with its "score". Is this even possible with SQL Server?

So I guess +5 to each item for every time it occurred in column Growth1. +4 to for column Growth2 etc.

Is that the best way to go about creating a ranked list?

Desired output: possible

1   Market Share                   45
2   Disease Profile Development    30
3   Physician Recruitment          28
4   Referrals                      21
5   Splitters                      18

Answer 1

SELECT ROW_NUMBER() OVER (ORDER BY 
ISNULL(b.count_1,0) + 
ISNULL(c.count_2,0) + 
ISNULL(d.count_3,0) + 
ISNULL(e.count_4,0) + 
ISNULL(f.count_5,0) DESC) AS rank
,a.categories
,ISNULL(b.count_1,0) + ISNULL(c.count_2,0) + ISNULL(d.count_3,0) + ISNULL(e.count_4,0) + ISNULL(f.count_5,0) AS total_score FROM

(SELECT DISTINCT Growth1 AS categories FROM [TESTDB].[dbo].[testtable]
UNION SELECT DISTINCT Growth2 AS categories FROM [TESTDB].[dbo].[testtable]
UNION SELECT DISTINCT  Growth3 AS categories FROM [TESTDB].[dbo].[testtable]
UNION SELECT DISTINCT  Growth4 AS categories FROM [TESTDB].[dbo].[testtable]
UNION SELECT DISTINCT  Growth5 AS categories FROM [TESTDB].[dbo].[testtable]) AS a

LEFT JOIN
(SELECT Growth1, COUNT(Growth1) * 5 AS count_1
FROM  [TESTDB].[dbo].[testtable]
GROUP BY Growth1) AS b
ON a.categories = b.Growth1

LEFT JOIN
(SELECT Growth2, COUNT(Growth2) * 4  AS count_2
FROM  [TESTDB].[dbo].[testtable]
GROUP BY Growth2) AS c
ON a.categories = c.Growth2

LEFT JOIN
(SELECT Growth3, COUNT(Growth3) * 3  AS count_3
FROM  [TESTDB].[dbo].[testtable]
GROUP BY Growth3) AS d
ON a.categories = d.Growth3

LEFT JOIN
(SELECT Growth4, COUNT(Growth4) * 2  AS count_4
FROM  [TESTDB].[dbo].[testtable]
GROUP BY Growth4) AS e
ON a.categories = e.Growth4

LEFT JOIN
(SELECT Growth5, COUNT(Growth5) * 1  AS count_5
FROM  [TESTDB].[dbo].[testtable]
GROUP BY Growth5) AS f
ON a.categories = f.Growth5