Thanks very much Paul for the detailed answer.

I'll need to spend some time working through it to make sure that I understand it all.

Something like this perhaps?

DECLARE @Item TABLE (ID INT, [HierarchyId] INT)

INSERT INTO @Item SELECT 1,103 UNION ALL SELECT 2,104

DECLARE @Hierarchy TABLE ([HierarchyID] INT, Name VARCHAR(5), ParentHierarchyId INT)

INSERT INTO...

Colin,

Congratulations on getting the attention so quickly of the master of bin-packing (Hugo Kornelis). I am but a shadowy, wannabe player in this space.

Nonetheless, I shall attempt to contribute...

With 48 points to your credit, I'd think you should know that the forum helpers would be much obliged if you could provide DDL and consumable sample data like this:

CREATE...

Glad you liked the response and the fish! 😀

newbie2 (2/12/2013)

---

Yes, that looks much cleaner. Unfortunately I can't seem to get it to translate to my data:

SELECT MyAddress, MyAddress as [address1], [address2]=LEFT([address2], CHARINDEX(' ', [address2])-1)

FROM MyTable

-- Remove...

Lowell's approach will be faster but here's one that is universal:

A SQL-Based Universal Currency Formatter[/url]

You could use this approach to format the digits groupings:

A SQL-Based Universal Currency Formatter[/url]

Perhaps something like this?

WITH MyData ([address]) AS (

SELECT '1 north main st'

UNION ALL SELECT '12 main street'

UNION ALL...

I believe this article by Jeff Moden is exactly what you need:

http://www.sqlservercentral.com/articles/Crosstab/65048/

As you're new to SSC, I'd like to suggest that to get better and faster help, it helps your helpers if you'll post DDL and consumable sample data like this:

CREATE...

Steve Jones - SSC Editor (2/12/2013)

---

dwain.c (2/10/2013)

---

SQLRNNR (2/10/2013)

---

L' Eomot Inversé (2/8/2013)

---

Lynn Pettis (2/8/2013)

---

Not really,...

Paul White (2/12/2013)

---

If you look closely at the...

Sean Lange (2/12/2013)

---

Very interesting Dwain. I tried another idea to see how it would hold up. It seems that it is possible to use another temp table to hold the...

Sean Lange (2/12/2013)

---

ahpitre (2/12/2013)

---

Great. Thanks for the prompt response. Do you think that the code could be modified to be recursive, in other words, if I send 1 delimiter, code...