mol mol - 1 month ago 13
SQL Question

MS SQL 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:

Column: Item 1 | Item 2 | Item 3 | Item 4 |
Order1: Hamburger Fries Soda Salad
Order2: Fries Hamburger Soda
Order3: Salad Soda Soda
Order4: 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