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.
BSCS 1992 Wentworth Institute of Technology
Originally from the Boston (MA) area. Lived in SoCal for a while. Now in the Phoenix (AZ) area.
OpenVMS enthusiast, ISO 8601 evangelist, photographer, opinionated SOB, acknowledged contrarian
"If you need help knowing what to think, let me know and I'll tell you." -- Jeffrey Snover [MSFT]
"Typing is no substitute for thinking." -- R.W. Hamming
"I find it appalling that you can become a programmer with less training than it takes to become a plumber." -- Bjarne Stroustrup
ZagNut’s Law: Arrogance is inversely proportional to ability.
"Well blow me sideways with a plastic marionette. I've just learned something new - and if I could award you a 100 for that post I would. Way to go you keyboard lovegod you." -- Pete O'Hanlon
"linq'ish" sounds like "inept" in German -- Andreas Gieriet
"Things would be different if I ran the zoo." -- Dr. Seuss
"Wrong is evil, and it must be defeated." – Jeff Ello
"A good designer must rely on experience, on precise, logical thinking, and on pedantic exactness." -- Nigel Shaw
“It’s always easier to do it the hard way.” -- Blackhart
“If Unix wasn’t so bad that you can’t give it away, Bill Gates would never have succeeded in selling Windows.” -- Blackhart
"Omit needless local variables." -- Strunk... had he taught programming
"We learn more from our mistakes than we do from getting it right the first time."
My first rule of debugging: "If you get a different error message, you're making progress."
My golden rule of database management: "Do not unto others' databases as you would not have done unto yours."
My general rule of software development: "Design should be top-down, but implementation should be bottom-up."