[Solved] Selecting distinct values from multiple column of a table with their count

by


Get all column values to one row and find the count

SQL SERVER 

;WITH CTE AS
(
   SELECT COL1 Name
   FROM YOURTABLE
   UNION ALL
   SELECT COL2
   FROM YOURTABLE
   UNION ALL
   SELECT COL3
   FROM YOURTABLE
   UNION ALL
   SELECT COL4
   FROM YOURTABLE
   UNION ALL
   SELECT COL6
   FROM YOURTABLE
   UNION ALL
   SELECT COL7
   FROM YOURTABLE
)
SELECT DISTINCT Name,COUNT(Name) OVER(PARTITION BY Name) [COUNT]
FROM CTE

MYSQL

SELECT Name,COUNT(*) [COUNT]
(
   SELECT COL1 Name
   FROM #TEMP
   UNION ALL
   SELECT COL2
   FROM #TEMP  
   UNION ALL
   SELECT COL3
   FROM #TEMP  
   UNION ALL
   SELECT COL4
   FROM #TEMP  
   UNION ALL
   SELECT COL6
   FROM #TEMP  
   UNION ALL
   SELECT COL7
   FROM #TEMP      
)TAB   
GROUP BY Name

5

solved Selecting distinct values from multiple column of a table with their count