If you have a table like:
Ford Model A
and you want something like:
Make Models Count
------- ------------------------------- -----
Ford Thunderbird , Pinto , Model A 3
Nash Rambler 1
Toyota Yaris , Corrolla 2
you'll need to write some code -- SQL Server doesn't have that built in (yet).
You can write a function to do it, you can write a custom CLR aggregator to do it, but I wanted to see if I could use a Common Table Expression to do it.
The following is such a CTE:
WITH cte AS
, COUNT(*) [Count]
, MAX(Model) Models
, 0 [Rank]
GROUP BY Make
, Models + N' , ' + Model
, [Rank] + 1
INNER JOIN MakeModel
AND cte.Models NOT LIKE '%' + Model + '%'
SELECT Make,MAX(Models) Models,MAX([Count]) [Count] FROM cte GROUP BY Make ORDER BY Make
0) You may need to CONVERT your values to
1) You may need to protect against
2) I don't think this will scale well
3) I can't think of a way to get the values listed ascending rather than descending
This was mainly an interesting exercise, I don't expect to use it in production systems.