CODEWITHSUNDEEP
sqlsql-serverunpivot
Andy
Andy

Reputation: 25

SQL Server : return most frequently occurring values (and counts) from across 9 columns in one table

I have a SQL Server 2005 database with a table (ExpenseFormItems) that (among other things) stores street addresses across 11 columns (fromtravel, totravel, totravel1, totravel2, totravel3....totravel9).

Basically a trip with 11 total legs/stops, as well as Google calculated mileage/etc (not important here).

I would like to be able to return the (say 15) most frequently occurring addresses across all 11 columns, as well as the number of times they occur.

So basically

[TO]             [Occurrances]
==============
address1               328
address2               233
address3               112
....
address15               23

I'm guessing some sort of un/pivot will be in use here, but I've never done anything cool enough to have used one before, so am not grasping how to apply (what I've read about them) to this case.

TIA

Upvotes: 1

Views: 604

Answers (1)

Taryn
Taryn

Reputation: 247720

It sounds like you want to UNPIVOT the data which will take the data from columns and convert it into rows.

The basic structure will be:

select col, address
from ExpenseFormItems
unpivot
(
  address
  for col in (fromtravel, totravel, totravel1,
              totravel2, totravel3, totravel4,
              totravel5, totravel6, totravel7, 
              totravel8, totravel9)
) unpiv

Then if you want to find the number of occurrences, then you can use:

select address, count(address) occurrences
from
(
  select col, address
  from ExpenseFormItems
  unpivot
  (
    address
    for col in (fromtravel, totravel, totravel1,
                totravel2, totravel3, totravel4,
                totravel5, totravel6, totravel7, 
                totravel8, totravel9)
  ) unpiv
) d
group by address 
order by occurrences desc;

If you then wanted to return the 15 most frequent addresses, then you could add a TOP 15 to the SELECT.

Upvotes: 2

Related Questions