Thanks Lutz and Kevin...

This is a double WIN for me... not only did you point me to the exact solution I was looking for

but you also introduced me to...

OK....

I managed to find a solution... however it doesn't seem very elegant to me....

I would welcome any input to make the query more concise or more efficient.

WITH cteContiguous

AS

(

--define the...

Paul White NZ (4/20/2010)

---

COldCoffee (4/20/2010)

---

Paul, i initially coded both pivot-join and nested-pivot solution which is exactly similar to yours, but i feared tat 2 pivots and a join willl over-load...

Thanks COld

I love seeing how the same problem can be approached from different angles.

To tell the truth I tried to use the pivot operator with the data set

but couldn't get...

Thanks Jeff. That's great!

Thanks Lutz

I'll have a look at the links you provided.

Hi Paul

Your right about the top 100 percent....

I'll have another go when I have time to find a work around.

pYak

Hi Jeff.

As I said the solution works for me but is not very elegant.

Would you know of any other solutions that tackle the same problem?

Thanks

Well I've finally figured it out.

Thanks to everyone for the pointers.

I've posted the solution below for anyone who may have a similar problem.

Its not elegant but does the job.

--drop temporary...

Hi Jeff

I'm trying to extract a heirarcy from the csv value within each row

where you have a natural hierarchy eg grandson1->son1->grandfather1

and then relate that to the other rows in the...

Hi Lutx

Actually on second inspection I realised that I dont need the child_id in the #BOM table.

What would be helpful in its place would be a level_id which

represents the level...

Hi Elliott

Thanks for the reply.

I believe that the generated error message is generic and does not infer a specific language type...

so I think that it would just imply a string...

Hi Lutz

Sorry... my bad. That was a matter of shot then aim.

You are spot on with the view.

Thanks again for your help.

Cheers

pYak

Hi Lutz

Thanks again.

I didn't explain very well.

I intended the view to return the same resultset as your 1st post.

I guess the simple logic would be something like

SELECT

CAST(header +...

Hi Lutz

Thank you so much for your help!

I was hoping to construct the xml inline in a view

but it appears that I will need to use a stored proc now

to...