Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Get Count of Values from Multiple Columns

In this puzzle you have to find count of each value from multiple columns and rows. Please check out the sample input values and sample expected output below.

Sample Input

col1 col2 col3 col4 col5
300 400 401 300 500
301 200 990 1 300
499 488 455 333 222
4 4 3 3 4

Expected Output

vals cnt
1 1
3 2
4 3
200 1
222 1
300 3
301 1
333 1
400 1
401 1
455 1
488 1
499 1
500 1
990 1

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE MultipleColumnDuplicates
(
	 col1 INT
	,col2 INT
	,col3 INT
	,col4 INT 
	,col5 INT
)
GO

INSERT INTO MultipleColumnDuplicates VALUES
(300     ,   400    ,     401  ,   300   ,   500   ),  
(301     ,   200    ,     990  ,   1     ,   300   ),
(499     ,   488    ,     455  ,   333   ,   222   ),   
(4       ,     4    ,      3   ,    3    ,     4   )  									 
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--

SELECT z.vals, COUNT(*) cnt
FROM MultipleColumnDuplicates a
CROSS APPLY 
(
    VALUES(a.col1),(a.col2),(a.col3),(a.col4),(a.col5)
) z(vals)
GROUP BY z.vals

--

Solution 2


--

SELECT a Vals ,COUNT(*) cnt FROM
(
	SELECT col1 a FROM MultipleColumnDuplicates UNION ALL
	SELECT col2 FROM MultipleColumnDuplicates UNION ALL   
	SELECT col3 FROM MultipleColumnDuplicates UNION ALL  
	SELECT col4 FROM MultipleColumnDuplicates UNION ALL  
	SELECT col5 FROM MultipleColumnDuplicates 
)k
GROUP BY a

--

Solution 3 using UnPivot by Mithun Chandran


--

SELECT col,count(col) as valuecount FROM 
(SELECT * FROM MultipleColumnDuplicates )s 
UNPIVOT (col FOR Cols IN ( col1, col2, col3, col4, col5)) as m
group by col

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com