petervdkerk (7/28/2015)
@Jason: Thanks. But your query returns 552 rows for the few products I have and for the resulting rows that have a value for artikelid the artikelgroups are not concatenated...just try to run it on the data I provided.
Based on the data you provided, I'm getting 5 rows back.
I did find one screw up I forgot to fix before posting though... So here's the fix... (Also note that I'm not taking credit for this code... Like I said before, this is just a slight modification to Lynn's code.)
with rCTE as (
SELECT
pcm.artikelid,
ag.id,
ag.catlevel,
ag.slug_nl,
CatString = cast(ag.slug_nl as nvarchar(max)),
SortKey = cast(ag.id as varbinary(max))
from
[dbo].[articlegroups] ag
JOIN [dbo].[products_category_mapping] pcm--<<<<<<<<<<<<<<
ON ag.id = pcm.articlegroup_id--<<<<<<<<<<<<<<
where
ag.parentid = 0
union all
SELECT
pcm.artikelid,
ag.id,
ag.catlevel,
ag.slug_nl,
CatString = r.CatString + N' > ' + ag.slug_nl,
SortKey = r.SortKey + cast(ag.id as varbinary(max))
from
rCTE r
inner join [dbo].[articlegroups] ag
on (r.id = ag.parentid)
JOIN [dbo].[products_category_mapping] pcm--<<<<<<<<<<<<<<
ON ag.id = pcm.articlegroup_id--<<<<<<<<<<<<<<
)
select * from rCTE order by SortKey;
I'll post an alternate solution that may work for you as well... So keep your eyes peeled. 😉