Peter,

I did manage to find my SQL book in 5 minutes last night, but I then tried to apply it and got lost quickly. This morning, however, I think I got a good clear picture of what was needed, and so here's the code and the results:

THE CODE:

WITH GROUPING_VALUES AS (

SELECT PCM.artikelid, PCM.articlegroup_id, AG.catlevel, AG.slug_nl,

ROW_NUMBER() OVER(PARTITION BY PCM.artikelid ORDER BY PCM.articlegroup_id) - AG.catlevel AS GRP_VALUE

FROM dbo.products_category_mapping AS PCM

INNER JOIN dbo.articlegroups AS AG

ON PCM.articlegroup_id = AG.id

INNER JOIN dbo.products AS P

ON PCM.artikelid = P.id

)

SELECT DISTINCT GV.artikelid AS ProductID, GV.slug_nl + ' > ' +

STUFF(

(SELECT TOP (100) PERCENT N' > ' + GA.slug_nl

FROM GROUPING_VALUES AS GA

WHERE GA.artikelid = GV.artikelid

AND GA.GRP_VALUE = GV.GRP_VALUE

AND GA.catlevel <> 0

ORDER BY GA.catlevel

FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)')

, 1, 3, '') AS categorystring

FROM GROUPING_VALUES AS GV

WHERE GV.catlevel = 0

ORDER BY ProductID;

THE RESULTS:

ProductID categorystring

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

2481445 bruidstaart > taarttoppers > grappig

2481445 taarttoppers > grappig

2481446 bruidstaart > taarttoppers > grappig

2481446 taarttoppers > grappig

2481447 feestartikelen > ballonnen

2481448 feestartikelen > ballonnen

2481449 bruidstaart > taarttoppers > grappig

2481449 taarttoppers > grappig

2481450 bruidstaart > taarttoppers > grappig

2481450 taarttoppers > grappig

2481451 bruidstaart > taarttoppers > grappig

2481451 taarttoppers > grappig

2481452 bruidstaart > taarttoppers > grappig

2481452 taarttoppers > grappig

2481453 feestartikelen > ballonnen

2481454 feestartikelen > ballonnen

2481455 feestartikelen > ballonnen

2481456 feestartikelen > ballonnen

2481457 feestartikelen > ballonnen

2481458 feestartikelen > ballonnen

2481459 feestartikelen > ballonnen

2481460 feestartikelen > ballonnen

2481461 feestartikelen > ballonnen

2481462 feestartikelen > ballonnen

2481463 feestartikelen > ballonnen

2481464 feestartikelen > ballonnen

2481465 feestartikelen > ballonnen

2481466 accessoires > tiaras

2481467 accessoires > tiaras

2481468 accessoires > tiaras

(30 row(s) affected)

I used your most recent update to your data. Basically, I just had to establish the right group values. Let me know if I've missed anything, or gotten an incorrect result.

EDIT: There's a need to replace the XML representation of the greater than symbol with the actual greater than symbol, but this forum doesn't appear to be able to display the ampersand gt semicolon, so I bolded the two locations where the first needs to be the ampersand one and the 2nd needs to be the greater than.

EDIT2: The bold wasn't doing anything useful, so I added a space and a comment in the hopes that will make it more clear...

EDIT3: Forget about the two previous edits - they're no longer relevant. I updated the code to use Jason Long's modification on the FOR XML PATH portion of my query.