Click here to Skip to main content
12,406,528 members (30,626 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

27.7K views
10 bookmarked
Posted

Stupid CTE tricks -- string concatenation

, 25 Dec 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
Concatenating values from multiple rows into one string value via a Common Table Expression
If you have a table like:

Make    Model
------- -----------
Toyota  Yaris
Toyota  Corrolla
Ford    Pinto
Ford    Model A
Ford    Thunderbird
Nash    Rambler

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
(
  SELECT Make
  , COUNT(*) [Count]
  , MAX(Model) Models
  , 0 [Rank]
  FROM MakeModel
  GROUP BY Make
UNION ALL
  SELECT cte.Make
  , cte.[Count]
  , Models + N' , ' + Model
  , [Rank] + 1
  FROM cte
  INNER JOIN MakeModel
  ON cte.Make=MakeModel.Make
  AND cte.Models NOT LIKE '%' + Model + '%'
  AND cte.[Rank]<cte.[Count]
)
 
SELECT Make,MAX(Models) Models,MAX([Count]) [Count] FROM cte GROUP BY Make ORDER BY Make


Caveats:
0) You may need to CONVERT your values to NVARCHAR(MAX)
1) You may need to protect against NULL values
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

PIEBALDconsult
Software Developer (Senior)
United States United States
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 pedant and contrarian

---------------

"Using fewer technologies is better than using more." -- Rico Mariani

"Good code is its own best documentation. As you’re about to add a comment, ask yourself, ‘How can I improve the code so that this comment isn’t needed?’" -- Steve McConnell

"Every time you write a comment, you should grimace and feel the failure of your ability of expression." -- Unknown

"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

You may also be interested in...

Comments and Discussions

 
GeneralFor ordering ascending, try changing AND cte.[Rank] < ct... Pin
jsc423-Jan-12 1:11
memberjsc423-Jan-12 1:11 
General0) your input tables do have to be converted to nvarchar(max... Pin
Daniel Gidman21-Dec-11 7:57
memberDaniel Gidman21-Dec-11 7:57 
GeneralRe: 0) I know the ones shown don't 1) I think they might, but I ... Pin
PIEBALDconsult21-Dec-11 16:48
memberPIEBALDconsult21-Dec-11 16:48 
0) I know the ones shown don't
1) I think they might, but I didn't try it
2) Maybe not with this
3) I don't think so

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160730.1 | Last Updated 25 Dec 2011
Article Copyright 2011 by PIEBALDconsult
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid