mol mol - 1 month ago 7
SQL Question

SQL Server : count items in different columns and group them

Looked through a few posts and haven't found an workable answer to this yet.

Example of a table I have:

Item 1 | Item 2 | Item 3 | Item 4 |
-----------------------------------------------------
Hamburger Fries Soda Salad
Fries Hamburger Soda
Salad Soda Soda
Hamburger Fries


Then I'm trying to count and group them so they show up like this.

4 Soda
3 Hamburger
3 Fries
2 Salad

Answer

Unpivot the data and do the count

select Items,count(1)
from yourtable
cross apply(values (Item1),(Item2),(Item3),(Item4)) CA (items)
Group by Items