SQL statement for combining hierarchical categories into a single row

Question

I already checked this question , but this is different, since my objects can fall under several parent nodes, and not one, and I have an additional mapping table instead of all in one table.

I have a hierarchical structure for comparing products by categories, categories go to 3 levels by depth (depth is defined in groupgroups.catlevel, 0 is the main category and goes to a lower level of category 2). In addition, the product can be in more than 1 category (!).

product information is stored in [products]
Group articles are defined in [articlegroups]
and mapping products to article groups is defined in [products_category_mapping]

Now I want to get the index of the full category path for each item, so with the data below, I would expect these 2 lines as a result:

 id categorystring 2481446 Taarttoppers > Taarttoppers grap'pig 2481446 Bruidstaart > Taarttoppers > Grappig 

Now I can get individual fields using instructions like this:

 SELECT ga.slug_nl as slug_nl_0 FROM articlegroups ga INNER JOIN products_category_mapping pcm ON pcm.articlegroup_id=ga.id INNER JOIN products gp on gp.id=pcm.artikelid WHERE gp.id=2481446 

But this only gives me this result:

 taarttoppers grappig bruidstaart taarttoppers grappig 

However, I don’t know how to combine different levels of categories regarding the depth of this category level, and there should be a ">" between them.

script for tables + data

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[articlegroups]( [id] [int] IDENTITY(1,1) NOT NULL, [parentid] [int] NOT NULL, [catlevel] [tinyint] NOT NULL CONSTRAINT [DF_articlegroups_lvl0_catlevel] DEFAULT ((0)), [slug_nl] [nvarchar](50) NOT NULL, CONSTRAINT [PK_articlegroups] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[products] Script Date: 28-07-15 15:45:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[products]( [id] [int] IDENTITY(1,1) NOT NULL, [artikelnummer] [nvarchar](60) NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[products_category_mapping] Script Date: 28-07-15 15:45:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[products_category_mapping]( [artikelid] [int] NOT NULL, [articlegroup_id] [int] NOT NULL, [createdate] [datetime] NOT NULL CONSTRAINT [DF_products_category_mapping_createdate] DEFAULT (getdate()) ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[articlegroups] ON GO INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (1, 0, 0, N'taarttoppers') GO INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (2, 1, 1, N'grappig') GO INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (3, 0, 0, N'feestartikelen') GO INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (4, 3, 1, N'ballonnen') GO INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (5, 3, 1, N'slingers') GO INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (6, 0, 0, N'bruidstaart') GO INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (7, 6, 1, N'taarttoppers') GO INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (8, 7, 2, N'grappig') GO INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (9, 0, 0, N'accessoires') GO INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (10, 9, 1, N'tiaras') GO SET IDENTITY_INSERT [dbo].[articlegroups] OFF GO SET IDENTITY_INSERT [dbo].[products] ON GO INSERT [dbo].[products] ([id], [artikelnummer]) VALUES (2481446, N'1013') GO SET IDENTITY_INSERT [dbo].[products] OFF GO INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481446, 1, CAST(N'2015-07-24 20:27:02.890' AS DateTime)) GO INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481446, 2, CAST(N'2015-07-24 20:27:02.890' AS DateTime)) GO INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481446, 6, CAST(N'2015-07-24 20:27:02.890' AS DateTime)) GO INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481446, 7, CAST(N'2015-07-24 20:27:02.890' AS DateTime)) GO INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481446, 8, CAST(N'2015-07-24 20:27:02.890' AS DateTime)) GO /****** Object: Index [PK_products] Script Date: 28-07-15 15:45:03 ******/ ALTER TABLE [dbo].[products] ADD CONSTRAINT [PK_products] PRIMARY KEY NONCLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ALTER TABLE [dbo].[products_category_mapping] WITH CHECK ADD CONSTRAINT [FK_articlegroups_lvl1_mapping_products] FOREIGN KEY([artikelid]) REFERENCES [dbo].[products] ([id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[products_category_mapping] CHECK CONSTRAINT [FK_articlegroups_lvl1_mapping_products] GO 

Answer 1

The data model in which you store the entire hierarchy for the model helps you not to use recursion when retrieving groups, but if you can use it for a path, you will also need a group of top-level articles that must be stored for each row so that it can be use to group data. I made changes to the article table to contain toplevelid:

 id parentid catlevel toplevelid slug_nl 1 0 0 1 taarttoppers 2 1 1 1 grappig 3 0 0 3 feestartikelen 4 3 1 3 ballonnen 5 3 1 3 slingers 6 0 0 6 bruidstaart 7 6 1 6 taarttoppers 8 7 2 6 grappig 9 0 0 9 accessoires 10 9 1 9 tiaras 

This way you can just get names like this:

 SELECT tmp.toplevelid, categorystring = STUFF((SELECT N' > ' + slug_nl FROM articlegroups AS ga2 WHERE ga2.toplevelid = tmp.toplevelid ORDER BY catlevel FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 3, '') FROM products gp INNER JOIN products_category_mapping pcm ON gp.id=pcm.artikelid outer apply ( select distinct ga.toplevelid from articlegroups ga where pcm.articlegroup_id=ga.id ) tmp WHERE gp.id=2481446 GROUP BY tmp.toplevelid ORDER BY tmp.toplevelid; 

Example in SQL Fiddle .

The disadvantage of this design is that if you have changes in the hierarchy, you will have to update them for each product. Another option is to store items only to the lowest level and use a recursive CTE to retrieve the hierarchy. This is a simpler model to support, but it doesn't read as fast because recursion needs to be processed every time.