Aggregating quantities across multiple columns without a cursor or temporary table

If I have a table with two columns, A and B, and would like to count how many times an item shows up in both columns, is there a way to do so without using a cursor or temp table?

Ex:
Column A   Column B
BMW           Ford
Jaguar        BMW
Mercedes      Lexus
Chevrolet     Jaguar
BMW           Ford

I would like the result to be:

BMW        3
Jaguar     2
Mercedes   1
Chevrolet  1
Ford       2
Lex开发者_Python百科us      1

---

SELECT ColumnA, COUNT(*) as Times
FROM (
  SELECT ColumnA FROM ATable
  UNION ALL
  SELECT ColumnB FROM ATable) Joined
GROUP BY ColumnA

Most DBMS require you to alias the derived table

---

SELECT x, COUNT(*)
   FROM (SELECT ColumnA AS x FROM t
         UNION ALL
         SELECT ColumnB FROM t)
  GROUP BY x

---

You don't say DBMS. If SQL Server you can use UNPIVOT to avoid scanning the table twice.

SELECT car, COUNT(*)
FROM YourTable
UNPIVOT (car FOR col in (ColumnA, ColumnB)) u 
GROUP BY car

Depending on what indexes you have this could be either more or less efficient than the UNION ALL approach (If both ColumnA and ColumnB were indexed the UNION ALL query plan could do a merge join on the 2 indexes and feed that into the aggregation operator avoiding the need to sort the data that the UNPIVOT plan would require)