mol mol - 1 year ago 65
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download