for example lets say I have a dataset like this. I want to count the values, each value in multiple columns and same rows. As a result it has to say the count of the values I put into.

2A 1B and 
2C 1D

Anyone can help?

CodePudding user response：

You don't count values in a row, you count values in a column. So, you use sql to reformat your data in to a single column, then count the values in the usual way.

SELECT
  column_value,
  COUNT(*)
FROM
(
  SELECT col1 AS column_value FROM your_table
  UNION ALL
  SELECT col2 AS column_value FROM your_table
  UNION ALL
  SELECT col3 AS column_value FROM your_table
)
  AS pivoted
GROUP BY
  column_value
ORDER BY
  column_value

CodePudding user response：

As I commented, here is the code for the same. Can be optimized a bit by removing multiple select statements.

Declare @val Varchar(MAX); 
Select @val=COALESCE(@val   ' '   result, result) 
FROM (
SELECT CONCAT(cnt,col) result FROM (
SELECT col, count(*)cnt FROM (
SELECT col
FROM
(
  SELECT col1, col2, col3 
  from #temp
) AS cp
UNPIVOT 
(
  col FOR cols IN (col1, col2, col3 )
) AS up)t
group by col)t)t2

select @val