Selection of distinct values ​​from multiple columns in a table with their number

advertisements

i want to to select distinct values from multiple columns of same table with their count example

and the output should be like this


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