July 28, 2015 at 2:28 pm
I have a hierarchical structure for mapping products to categories, categories go 3 levels deep (depth is defined in articlegroups.catlevel, 0 being the main category and traversing down to lower category level 2). Also, a product may be in more than 1 category(!).
product details are stored in `[products]`
articlegroups are defined in `[articlegroups]`
and the mapping of the products to the articlegroups are defined in `[products_category_mapping]`
Now, I want to retrieve index the full category path for each item, so with the data provided below, I'd expect these 2 rows as a result:
id categorystring
2481446 Taarttoppers > Taarttoppers grap'pig
2481446 Bruidstaart > Taarttoppers > Grappig
Now I can get the separate fields via a statement 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 that just gives me this result:
taarttoppers
grappig
bruidstaart
taarttoppers
grappig
However, I don't know how to concatenate the different category levels respecting the depth of that category level and have a '>' character in between.
**script for tables+data**
USE [tt]
GO
/****** Object: Table [dbo].[articlegroups] Script Date: 29-07-15 09:22:58 ******/
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)),
[label_nl] [nvarchar](50) NOT NULL,
[label_en] [nvarchar](50) NOT NULL,
[slug_nl] [nvarchar](50) NOT NULL,
[slug_en] [nvarchar](50) NOT NULL,
[pagetitle_nl] [nvarchar](100) NULL,
[pagetitle_en] [nvarchar](100) NULL,
[image_nl] [nvarchar](50) NULL,
[image_en] [nvarchar](50) NULL,
[description_nl] [nvarchar](500) NOT NULL,
[description_en] [nvarchar](500) NULL,
[metadescription_nl] [nvarchar](200) NULL,
[metadescription_en] [nvarchar](200) NULL,
[createdate] [datetime] NOT NULL CONSTRAINT [DF_articlegroups_createdate] DEFAULT (getdate()),
[canonicalurl_nl] [nvarchar](150) NULL,
[canonicalurl_en] [nvarchar](150) 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: 29-07-15 09:22:58 ******/
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,
[titel] [nvarchar](200) NOT NULL,
[friendlyurl] [nvarchar](200) NULL,
[omschrijving] [nvarchar](3000) NULL,
[prijs] [int] NULL CONSTRAINT [DF_products_prijs] DEFAULT ((0)),
[prijseenheid] [nvarchar](50) NULL,
[artikelgroep] [nvarchar](150) NULL,
[artikelgroep_label] [nvarchar](150) NULL,
[subartikelgroep] [nvarchar](150) NULL,
[subartikelgroep_label] [nvarchar](150) NULL,
[subartikelgroep1] [nvarchar](150) NULL,
[subartikelgroep1_label] [nvarchar](150) NULL,
[foto] [nvarchar](500) NULL,
[foto_groot] [nvarchar](500) NULL,
[nvarchar](500) NULL,
[affiliatesite] [nvarchar](50) NULL,
[lang] [nvarchar](3) NULL,
[currency] [nvarchar](3) NULL,
[createdate] [datetime] NOT NULL CONSTRAINT [DF_products_createdate] DEFAULT (getdate()),
[updatedate] [datetime] NOT NULL CONSTRAINT [DF_products_updatedate] DEFAULT (getdate()),
[siteid] [tinyint] NOT NULL CONSTRAINT [DF_products_siteid] DEFAULT ((0)),
[tags] [nvarchar](250) NULL,
[extradata] [nvarchar](250) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[products_category_mapping] Script Date: 29-07-15 09:22:58 ******/
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], [label_nl], [label_en], [slug_nl], [slug_en], [pagetitle_nl], [pagetitle_en], [image_nl], [image_en], [description_nl], [description_en], [metadescription_nl], [metadescription_en], [createdate], [canonicalurl_nl], [canonicalurl_en]) VALUES (1, 0, 0, N'Taarttoppers', N'Cake toppers', N'taarttoppers', N'cake-toppers', N'Taarttoppers', N'Cake toppers', N'taarttoppers.png', N'taarttoppers.png', N'Taarttoppers descr', N'Cake toppers descr', N'Taarttoppers meta', N'Cake toppers meta', CAST(N'2015-07-15 11:19:43.700' AS DateTime), N'https://www.toptrouwen.nl/trouwshop/bruidstaart/taarttoppers', N'')
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [label_nl], [label_en], [slug_nl], [slug_en], [pagetitle_nl], [pagetitle_en], [image_nl], [image_en], [description_nl], [description_en], [metadescription_nl], [metadescription_en], [createdate], [canonicalurl_nl], [canonicalurl_en]) VALUES (2, 1, 1, N'Taarttoppers grap''pig', N'Cake toppers funny', N'grappig', N'funny', N'Grappige taarttoppers test apostrophe ''', N'Funny Cake toppers', NULL, NULL, N'Taarttoppers grappig descr test apostrophe ''', N'Cake toppers funny descr', N'Taarttoppers grappig meta test apostrophe ''', N'Cake toppers funny meta', CAST(N'2015-07-15 11:19:43.700' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [label_nl], [label_en], [slug_nl], [slug_en], [pagetitle_nl], [pagetitle_en], [image_nl], [image_en], [description_nl], [description_en], [metadescription_nl], [metadescription_en], [createdate], [canonicalurl_nl], [canonicalurl_en]) VALUES (3, 0, 0, N'Feestartikelen', N'Party stuff', N'feestartikelen', N'party-stuff', N'Feestartikelen', N'Party stuff', NULL, NULL, N'Feestartikelen descr', N'Party stuff desc', N'Feestartikelen meta', N'Party stuff meta', CAST(N'2015-07-16 14:10:18.083' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [label_nl], [label_en], [slug_nl], [slug_en], [pagetitle_nl], [pagetitle_en], [image_nl], [image_en], [description_nl], [description_en], [metadescription_nl], [metadescription_en], [createdate], [canonicalurl_nl], [canonicalurl_en]) VALUES (4, 3, 1, N'Ballonnen', N'Balloons', N'ballonnen', N'balloons', N'Ballonnen', N'Balloons', N'', N'', N'<p>Ballonnen descr 1</p>
', N'<p>Balloons desc 1</p>
', N'Ballonnen meta 1', N'Balloons meta 1', CAST(N'2015-07-16 16:38:38.453' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [label_nl], [label_en], [slug_nl], [slug_en], [pagetitle_nl], [pagetitle_en], [image_nl], [image_en], [description_nl], [description_en], [metadescription_nl], [metadescription_en], [createdate], [canonicalurl_nl], [canonicalurl_en]) VALUES (5, 3, 1, N'Slingers', N'Slingers-en', N'slingers', N'slingers-en', N'Slingers', N'Slingers en', N'', N'', N'<p>Slingers descr</p>
', N'<p>Slingers en desc</p>
', N'Slingers meta', N'Slingers en meta', CAST(N'2015-07-16 19:18:31.650' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [label_nl], [label_en], [slug_nl], [slug_en], [pagetitle_nl], [pagetitle_en], [image_nl], [image_en], [description_nl], [description_en], [metadescription_nl], [metadescription_en], [createdate], [canonicalurl_nl], [canonicalurl_en]) VALUES (6, 0, 0, N'Bruidstaart', N'Wedding cake', N'bruidstaart', N'wedding-cake', N'Bruidstaart', N'Wedding cake', N'', N'', N'<p>Bruidstaart descr</p>
', N'<p>Wedding cake descr</p>
', N'Bruidstaart meta', N'Wedding cake meta', CAST(N'2015-07-20 13:06:13.403' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [label_nl], [label_en], [slug_nl], [slug_en], [pagetitle_nl], [pagetitle_en], [image_nl], [image_en], [description_nl], [description_en], [metadescription_nl], [metadescription_en], [createdate], [canonicalurl_nl], [canonicalurl_en]) VALUES (7, 6, 1, N'Taarttoppers', N'Cake toppers', N'taarttoppers', N'cake-toppers', N'Taarttoppers', N'Cake toppers', N'', N'', N'<p>Taarttoppers descr</p>
', N'<p>Cake toppers descr</p>
', N'Taarttoppers meta', N'Cake toppers meta', CAST(N'2015-07-20 13:26:44.243' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [label_nl], [label_en], [slug_nl], [slug_en], [pagetitle_nl], [pagetitle_en], [image_nl], [image_en], [description_nl], [description_en], [metadescription_nl], [metadescription_en], [createdate], [canonicalurl_nl], [canonicalurl_en]) VALUES (8, 7, 2, N'Grappig', N'Funny', N'grappig', N'funny', N'Grappig', N'Funny', N'', N'', N'<p>Grappig descr</p>
', N'<p>Funny descr</p>
', N'Grappig meta', N'Funnymeta', CAST(N'2015-07-20 13:27:55.660' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [label_nl], [label_en], [slug_nl], [slug_en], [pagetitle_nl], [pagetitle_en], [image_nl], [image_en], [description_nl], [description_en], [metadescription_nl], [metadescription_en], [createdate], [canonicalurl_nl], [canonicalurl_en]) VALUES (9, 0, 0, N'Accessoires', N'Assecoiries', N'accessoires', N'accessories', N'Accessoires', N'Accessories', N'', N'', N'<p>Accessoires descr</p>
', N'<p>Accessories descr</p>
', N'Accessoires meta', N'Accessories meta', CAST(N'2015-07-25 11:11:37.363' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [label_nl], [label_en], [slug_nl], [slug_en], [pagetitle_nl], [pagetitle_en], [image_nl], [image_en], [description_nl], [description_en], [metadescription_nl], [metadescription_en], [createdate], [canonicalurl_nl], [canonicalurl_en]) VALUES (10, 9, 1, N'Tiara''s', N'Tiaras', N'tiaras', N'tiaras', N'Tiara''s', N'Tiaras', N'', N'', N'<p>Tiara's desc</p>
', N'<p>Tiaras desc</p>
', N'Tiara''s meta', N'Tiaras meta', CAST(N'2015-07-25 11:14:48.553' AS DateTime), NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[articlegroups] OFF
GO
SET IDENTITY_INSERT [dbo].[products] ON
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481449, N'10102', N'Taarttopper Now I have you', N'taarttopper-now-i-have-you', N'Taarttopper Now I have you van een bruid die haar bruidegom aan zijn jas meesleept. Taarttopper Now I have you van een bruid die haar bruidegom aan zijn jas meesleept.', 2995, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.893' AS DateTime), CAST(N'2015-07-25 11:34:32.033' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481450, N'10121', N'Taarttopper you come with me', N'taarttopper-you-come-with-me', N'Taarttopper You come with me is een humoristisch als versiering voor de bruidstaart. Taarttopper You come with me van een bruid die haar bruidegom aan zijn das meesleept. Deze taarttopper is een geweldige decoratie voor je bruiloft!', 2295, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.893' AS DateTime), CAST(N'2015-07-25 11:34:32.043' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481451, N'10122', N'Taarttopper over the shoulder', N'taarttopper-over-the-shoulder', N'Taarttopper Over the shoulder - De bruid neemt de bruidegom over haar schouder mee. Leuke taarttopper van een bruid neemt die de bruidegom over haar schouder meeneemt.', 2295, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.897' AS DateTime), CAST(N'2015-07-25 11:34:32.043' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481452, N'10123', N'Taarttopper Hop on!', N'taarttopper-hop-on', N'Taarttopper Hop On, een originele en humoristische taarttopper Hop on voor jullie bruidstaart. Een mooie taarttopper Hop On voor jullie bruidstaart. Echt een originele en humoristische decoratie voor je bruiloft.', 2295, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.897' AS DateTime), CAST(N'2015-07-25 11:34:32.047' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481446, N'1013', N'Taarttopper Oh no you don''t', N'taarttopper-oh-no-you-dont', N'Taarttopper Over the shoulder - De bruid neemt de bruidegom over haar schouder mee. Leuke taarttopper van een bruid neemt die de bruidegom over haar schouder meeneemt.', 2995, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.890' AS DateTime), CAST(N'2015-07-25 11:34:31.987' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481459, N'101348', N'Ballongewicht Rode Hartjes', N'ballongewicht-rode-hartjes', N'Ballongewicht Rode Hartjes voor helium ballonnen op bruiloft, huwelijk of trouwen. Een Ballon met helium aan een touwje met een Ballongewicht eraan. Zo zorg je ervoor dat de leuke versiering niet wegvliegt tijdens de bruiloft. De Ballongewichten zijn romantisch vormgegeven als twee rode hartjes.', 249, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.903' AS DateTime), CAST(N'2015-07-25 11:34:32.247' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481460, N'101391', N'Ballonnen metallic wit - 100 stuks', N'ballonnen-metallic-wit-100-stuks', N'Metallic witte ballonnen tijdens het Trouwen, Bruiloft of Huwelijk hebben een mooiere glans dan gewone ballonnen en verpakt per 100 stuks. Ter versiering of decoratie metallic witte ballonnen tijdens het Trouwen, Bruiloft of Huwelijk en verpakt per 100 stuks.', 1950, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.903' AS DateTime), CAST(N'2015-07-25 11:34:32.253' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481461, N'101410', N'Ballongewicht Zilveren Hartjes', N'ballongewicht-zilveren-hartjes', N'Ballongewicht Zilveren Hartjes voor Helium Ballonnen op bruiloft, huwelijk of trouwen. Een Ballon met helium aan een touwje met een Ballongewicht eraan. Zo zorg je ervoor dat de leuke versiering niet wegvliegt tijdens de bruiloft. De Ballongewichten zijn romantisch vormgegeven als twee zilveren hartjes.', 249, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.903' AS DateTime), CAST(N'2015-07-25 11:34:32.260' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481462, N'101415', N'Ballonnen hart wit - 100 stuks', N'ballonnen-hart-wit-100-stuks', N'Ballonnen - Hartvormige witte ballonnen tijdens het Trouwen, Bruiloft of Huwelijk verpakt per 100 stuks Als versiering of decoratie hartvormige witte ballonnen tijdens het Trouwen, Bruiloft of Huwelijk verpakt per 100 stuks', 1950, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.907' AS DateTime), CAST(N'2015-07-25 11:34:32.267' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481463, N'101519', N'Ballonnen hart rood - 100 stuks', N'ballonnen-hart-rood-100-stuks', N'ballonnen - rode hart-vormige ballonnen tijdens het Trouwen, Bruiloft of Huwelijk en verpakt per 10 stuks. Ter versiering of decoratie rode hartvormige ballonnen tijdens het Trouwen, Bruiloft of Huwelijk en verpakt per 10 stuks.', 1950, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.907' AS DateTime), CAST(N'2015-07-25 11:34:32.273' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481464, N'101631', N'Shark Air Swimmer', N'shark-air-swimmer', N'Shark Air Swimmer - Bestuurbare Helium Vis Air Swimmers is de nieuwste rage van Nederland. De Air Swimmers zijn op afstand bestuurbare helium vissen waarmee u door de lucht kunt zwemmen.', 4995, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.907' AS DateTime), CAST(N'2015-07-25 11:34:32.280' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481465, N'101648', N'Ballonnen metallic wit - 10 stuks', N'ballonnen-metallic-wit-10-stuks', N'Metallic witte ballonnen tijdens het Trouwen, Bruiloft of Huwelijk hebben een mooiere glans dan gewone ballonnen en verpakt per 10 stuks. Ter versiering of decoratie metallic witte ballonnen tijdens het Trouwen, Bruiloft of Huwelijk en verpakt per 10 stuks.', 225, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.907' AS DateTime), CAST(N'2015-07-25 11:34:32.283' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481453, N'10184', N'Ballonnen rond rood - 10 stuks', N'ballonnen-rond-rood-10-stuks', N'Rode ballonnen als versiering of decoratie voor de Bruiloft, Trouwen of Huwelijk. Ter versiering of decoratie rode ballonnen tijdens het Trouwen, Bruiloft of Huwelijk en verpakt per 10 stuks. De kleur en vorm van de liefde geeft een romantische uitstraling aan je bruiloft.', 200, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.897' AS DateTime), CAST(N'2015-07-25 11:34:32.077' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481468, N'10189', N'Diadeem gevlochten kristallen', N'diadeem-gevlochten-kristallen', N'Diadeem gevlochten kristallen - Haar versiering voor tijdens je huwelijk of trouwen - trouwartikel Diadeem gevlochten kristallen - Haar versiering voor tijdens je huwelijk of trouwen - trouwartikel', 7295, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-25 11:34:32.077' AS DateTime), CAST(N'2015-07-25 11:34:32.077' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481466, N'1033', N'Tiara white pearl waves', N'tiara-white-pearl-waves', N'Tiara White Pearl waves is een mooie combinatie van parels en kristal. - Haar versiering voor tijdens je huwelijk of trouwen - trouwartikel Tiara White Pearl waves is een mooie combinatie van parels en kristal. - Haar versiering voor tijdens je huwelijk of trouwen - trouwartikel', 6495, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-25 11:34:31.997' AS DateTime), CAST(N'2015-07-25 11:34:31.997' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481467, N'1036', N'Tiara white pearl crest', N'tiara-white-pearl-crest', N'Tiara White Pearl Crest combinatie van pareltjes en kristallen - Een elegante haar versiering voor tijdens je huwelijk of trouwen - trouwartikel Tiara White Pearl Crest combinatie van pareltjes en kristallen - Een elegante haar versiering voor tijdens je huwelijk of trouwen - trouwartikel', 6995, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-25 11:34:32.003' AS DateTime), CAST(N'2015-07-25 11:34:32.003' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481445, N'106', N'Taarttopper upper hand', N'taarttopper-upper-hand', N'Taarttopper upper hand van een bruid die haar bruidegom aan zijn benen meesleept. Taarttopper upper hand van een bruid die haar bruidegom aan zijn benen meesleept.', 3495, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.887' AS DateTime), CAST(N'2015-07-25 11:34:31.970' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481447, N'1070', N'Ballonnen rond wit - 10 stuks', N'ballonnen-rond-wit-10-stuks', N'Ballonnen - Witte ballonnen tijdens het Trouwen, Bruiloft of Huwelijk als versiering of decoratie. Ter versiering of decoratie Witte hartvormige ballonnen tijdens het Trouwen, Bruiloft of Huwelijk en verpakt per 10 stuks.', 175, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.893' AS DateTime), CAST(N'2015-07-25 11:34:32.027' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481448, N'1077', N'Lintrolletje wit', N'lintrolletje-wit', N'Een lintrolletje om al uw ballonnen mee op te hangen! Een lintrolletje om al uw ballonnen mee op te hangen!', 99, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.893' AS DateTime), CAST(N'2015-07-25 11:34:32.030' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481454, N'10838', N'Ballonnen hart wit - 8 stuks', N'ballonnen-hart-wit-8-stuks', N'Ballonnen - Hartvormige witte ballonnen tijdens het Trouwen, Bruiloft of Huwelijk verpakt per 8 stuks Als versiering of decoratie hartvormige witte ballonnen tijdens het Trouwen, Bruiloft of Huwelijk verpakt per 8 stuks', 210, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.900' AS DateTime), CAST(N'2015-07-25 11:34:32.133' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481455, N'10884', N'Ballonnen hart rood - 8 stuks', N'ballonnen-hart-rood-8-stuks', N'ballonnen - rode hart-vormige ballonnen tijdens het Trouwen, Bruiloft of Huwelijk en verpakt per 10 stuks. Ter versiering of decoratie rode hartvormige ballonnen tijdens het Trouwen, Bruiloft of Huwelijk en verpakt per 10 stuks.', 210, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.900' AS DateTime), CAST(N'2015-07-25 11:34:32.143' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481456, N'10886', N'Ballonnen Love is...to be Married - 8 stuks', N'ballonnen-love-isto-be-married-8-stuks', N'Ballonnen Love is...to be Married - 8 stuks Witte metallic latexballonnen bedrukt met witte inkt. Met de tekst: Love is... to be Married en zijn 30 cm groot.', 275, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.900' AS DateTime), CAST(N'2015-07-25 11:34:32.143' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481457, N'10975', N'Ballonnen Just Married - 10 stuks', N'ballonnen-just-married-10-stuks', N'Just Married Ballonnen Originele Ivoor Ballon Balonnen per 10 stuks', 350, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.900' AS DateTime), CAST(N'2015-07-25 11:34:32.163' AS DateTime), 0, NULL, N'')
GO
INSERT [dbo].[products] ([id], [artikelnummer], [titel], [friendlyurl], [omschrijving], [prijs], [prijseenheid], [artikelgroep], [artikelgroep_label], [subartikelgroep], [subartikelgroep_label], [subartikelgroep1], [subartikelgroep1_label], [foto], [foto_groot], , [affiliatesite], [lang], [currency], [createdate], [updatedate], [siteid], [tags], [extradata]) VALUES (2481458, N'10976', N'Ballonnen Just Married - 50 stuks', N'ballonnen-just-married-50-stuks', N'Just Married Ballonnen Originele Ivoor Ballon Balonnen per 50 stuks', 1500, N'', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', N'', N'', N'nl', N'EUR', CAST(N'2015-07-24 20:27:02.900' AS DateTime), CAST(N'2015-07-25 11:34:32.167' AS DateTime), 0, NULL, N'')
GO
SET IDENTITY_INSERT [dbo].[products] OFF
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481445, 1, CAST(N'2015-07-24 20:27:02.887' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481445, 2, CAST(N'2015-07-24 20:27:02.887' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481445, 6, CAST(N'2015-07-24 20:27:02.890' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481445, 7, CAST(N'2015-07-24 20:27:02.890' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481445, 8, CAST(N'2015-07-24 20:27:02.890' AS DateTime))
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
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481447, 3, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481447, 4, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481448, 3, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481448, 4, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481449, 1, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481449, 2, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481449, 6, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481449, 7, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481449, 8, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481450, 1, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481450, 2, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481450, 6, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481450, 7, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481450, 8, CAST(N'2015-07-24 20:27:02.893' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481451, 1, CAST(N'2015-07-24 20:27:02.897' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481451, 2, CAST(N'2015-07-24 20:27:02.897' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481451, 6, CAST(N'2015-07-24 20:27:02.897' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481451, 7, CAST(N'2015-07-24 20:27:02.897' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481451, 8, CAST(N'2015-07-24 20:27:02.897' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481452, 1, CAST(N'2015-07-24 20:27:02.897' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481452, 2, CAST(N'2015-07-24 20:27:02.897' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481452, 6, CAST(N'2015-07-24 20:27:02.897' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481452, 7, CAST(N'2015-07-24 20:27:02.897' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481452, 8, CAST(N'2015-07-24 20:27:02.897' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481453, 3, CAST(N'2015-07-24 20:27:02.897' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481453, 4, CAST(N'2015-07-24 20:27:02.897' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481454, 3, CAST(N'2015-07-24 20:27:02.900' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481454, 4, CAST(N'2015-07-24 20:27:02.900' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481455, 3, CAST(N'2015-07-24 20:27:02.900' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481455, 4, CAST(N'2015-07-24 20:27:02.900' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481456, 3, CAST(N'2015-07-24 20:27:02.900' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481456, 4, CAST(N'2015-07-24 20:27:02.900' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481457, 3, CAST(N'2015-07-24 20:27:02.900' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481457, 4, CAST(N'2015-07-24 20:27:02.900' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481458, 3, CAST(N'2015-07-24 20:27:02.900' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481458, 4, CAST(N'2015-07-24 20:27:02.900' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481459, 3, CAST(N'2015-07-24 20:27:02.903' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481459, 4, CAST(N'2015-07-24 20:27:02.903' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481460, 3, CAST(N'2015-07-24 20:27:02.903' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481460, 4, CAST(N'2015-07-24 20:27:02.903' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481461, 3, CAST(N'2015-07-24 20:27:02.907' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481461, 4, CAST(N'2015-07-24 20:27:02.907' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481462, 3, CAST(N'2015-07-24 20:27:02.907' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481462, 4, CAST(N'2015-07-24 20:27:02.907' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481463, 3, CAST(N'2015-07-24 20:27:02.907' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481463, 4, CAST(N'2015-07-24 20:27:02.907' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481464, 3, CAST(N'2015-07-24 20:27:02.907' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481464, 4, CAST(N'2015-07-24 20:27:02.907' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481465, 3, CAST(N'2015-07-24 20:27:02.907' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481465, 4, CAST(N'2015-07-24 20:27:02.907' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481466, 9, CAST(N'2015-07-25 11:34:31.997' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481466, 10, CAST(N'2015-07-25 11:34:32.000' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481467, 9, CAST(N'2015-07-25 11:34:32.003' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481467, 10, CAST(N'2015-07-25 11:34:32.003' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481468, 9, CAST(N'2015-07-25 11:34:32.077' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481468, 10, CAST(N'2015-07-25 11:34:32.077' AS DateTime))
GO
/****** Object: Index [PK_products] Script Date: 29-07-15 09:22:58 ******/
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
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0=tt, 1=huurwoningen' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'products', @level2type=N'COLUMN',@level2name=N'siteid'
GO
July 28, 2015 at 3:16 pm
Hi Peter,
So, you want to make a delimited string, where the delimiter is " > "? I think that this article[/url] will guide you in doing just that, just modify it for your needs.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 28, 2015 at 3:27 pm
I did not explain it correctly: the main problem is not creating a comma delimited string, the main problem is how to concatenate data from different levels in a hierarchical folder structure (where items in these folders may occur in multiple folders on different locations within this hierarchy).
July 28, 2015 at 3:32 pm
petervdkerk (7/28/2015)
I did not explain it correctly: the main problem is not creating a comma delimited string, the main problem is how to concatenate data from different levels in a hierarchical folder structure (where items in these folders may occur in multiple folders on different locations within this hierarchy).
It may appear heirarchical, but with the data provided, you don't actually have a true hierarchy, but some kind of hybrid, for which traditional heirarchical navigation techniques will fail. I've tried several things using FOR XML PATH and even a recursive CTE, and I can't find a way to navigate the path as there's no "certainty" to it. You have to have a single parent at the top in order to do this in a set-based fashion, and you don't. You have multiple hierarchies that overlap, and that's messy, at best. There may be no alternative but a CURSOR for this one, but I'd love to be proven wrong.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2015 at 3:44 pm
Thanks for your reply.
Actually there's no real overlap as far as I can see, except in the naming of the categories, but the actual categoryid as a node always has exactly 1 parent and not multiple, did you notice that?
You say this is messy and there's no certainty to my structure, which now brings doubts to my mind. Is my setup illogical?
I'd think that it should be possible for a product to be in multiple categories, for example a search on Best Buy on "DVD"
shows multiple categories in which I can find these products.
If I'm wrong please let me know, I'm at the start of my project so now I can still correct things 🙂
Thanks again!
July 28, 2015 at 3:55 pm
This doesn't answer the question directly, but hopefully it helps you come up with a solution.
with rCTE as (
select
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
where
ag.parentid = 0
union all
select
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)
)
select * from rCTE order by SortKey;
July 28, 2015 at 6:35 pm
petervdkerk (7/28/2015)
Thanks for your reply.Actually there's no real overlap as far as I can see, except in the naming of the categories, but the actual categoryid as a node always has exactly 1 parent and not multiple, did you notice that?
You say this is messy and there's no certainty to my structure, which now brings doubts to my mind. Is my setup illogical?
I'd think that it should be possible for a product to be in multiple categories, for example a search on Best Buy on "DVD"
shows multiple categories in which I can find these products.
If I'm wrong please let me know, I'm at the start of my project so now I can still correct things 🙂
Thanks again!
Ahhh... okay... If I had my druthers, I'd be setting up a Product table, a Categories table, and then the mapping table would just have a Product ID and a Category ID. I'm not sure why there's a need for a hierarchical level. You can certainly have an ordering field that could be used to prioritize which category has the most weight, or other similar situation, and it would appear in the mapping table. If there's something else about this setup that could benefit from a hierarchy, let me know. With that kind of setup, getting a list of category's for each product is relatively simple via a sub-query within the SELECT that chooses what products to display the list of categories for. It would use FOR XML PATH('').
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2015 at 6:55 pm
Ahhh... okay... If I had my druthers, I'd be setting up a Product table, a Categories table, and then the mapping table would just have a Product ID and a Category ID. I'm not sure why there's a need for a hierarchical level. You can certainly have an ordering field that could be used to prioritize which category has the most weight, or other similar situation, and it would appear in the mapping table. If there's something else about this setup that could benefit from a hierarchy, let me know. With that kind of setup, getting a list of category's for each product is relatively simple via a sub-query within the SELECT that chooses what products to display the list of categories for. It would use FOR XML PATH('').
Well, as you can see I have a Products table, Categories table (articlegroups) and a mapping table with just a Product ID and a Category ID (products_category_mapping).
The reason I need a hierarchy is because I want users to be able to drill down in categories. I now think that only the articlegroups.catlevel field is a bit obsolete as the hierarchy can already be derived from the fact that a category at most has a single parent. However, if I left out the catlevel field I would have to calculate the level of that articlegroup in each query, making it kind of expensive.
Does that make sense? 🙂
I think the suggestion from Lynn Pettis is pretty close, although not sure how to finalize that one yet.
July 28, 2015 at 7:35 pm
petervdkerk (7/28/2015)
Ahhh... okay... If I had my druthers, I'd be setting up a Product table, a Categories table, and then the mapping table would just have a Product ID and a Category ID. I'm not sure why there's a need for a hierarchical level. You can certainly have an ordering field that could be used to prioritize which category has the most weight, or other similar situation, and it would appear in the mapping table. If there's something else about this setup that could benefit from a hierarchy, let me know. With that kind of setup, getting a list of category's for each product is relatively simple via a sub-query within the SELECT that chooses what products to display the list of categories for. It would use FOR XML PATH('').
Well, as you can see I have a Products table, Categories table (articlegroups) and a mapping table with just a Product ID and a Category ID (products_category_mapping).
The reason I need a hierarchy is because I want users to be able to drill down in categories. I now think that only the articlegroups.catlevel field is a bit obsolete as the hierarchy can already be derived from the fact that a category at most has a single parent. However, if I left out the catlevel field I would have to calculate the level of that articlegroup in each query, making it kind of expensive.
Does that make sense? 🙂
I think the suggestion from Lynn Pettis is pretty close, although not sure how to finalize that one yet.
I think I see now... you are basically implementing sub-categories. If you just have one ultimate parent named "All Categories", you can have a normal hierarchy that can be easily navigated with traditional techniques. You can then have another field in the Category table for the parent category ID, and be good to go. With this kind of setup, you won't necessarily be limited to just two levels of categories, either.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2015 at 8:03 pm
you are basically implementing sub-categories
.
Correct.
If you just have one ultimate parent named "All Categories", you can have a normal hierarchy that can be easily navigated with traditional techniques. You can then have another field in the Category table for the parent category ID, and be good to go. With this kind of setup, you won't necessarily be limited to just two levels of categories, either.
Well...the way I've set it up now does not limit me to 2 levels either right? It's just that the data I provided coincidentally has 2 levels.
I'm still not sure why I would need this ultimate parent category, basically all categories at level 0 can have subcategories but have no parents, so IMO these would all function as ultimate categories for their respective subcategories. The categories themselves do not overlap, it's just the products I sell that may be in multiple categories.
If that still makes sense...can you help?
Thanks again! 🙂
July 28, 2015 at 8:15 pm
petervdkerk (7/28/2015)
you are basically implementing sub-categories
.
Correct.
If you just have one ultimate parent named "All Categories", you can have a normal hierarchy that can be easily navigated with traditional techniques. You can then have another field in the Category table for the parent category ID, and be good to go. With this kind of setup, you won't necessarily be limited to just two levels of categories, either.
Well...the way I've set it up now does not limit me to 2 levels either right? It's just that the data I provided coincidentally has 2 levels.
I'm still not sure why I would need this ultimate parent category, basically all categories at level 0 can have subcategories but have no parents, so IMO these would all function as ultimate categories for their respective subcategories. The categories themselves do not overlap, it's just the products I sell that may be in multiple categories.
If that still makes sense...can you help?
Thanks again! 🙂
The benefit to the single top level parent is that traditional hierarchical queries work quite well, and are reasonably well-known. Creating the navigation query is much easier than having multiple top level categories. You don't necessarily have to ever expose that uppermost level on the website if you don't want to, and that's easy enough to do as well. I'm going to have to go find the hierarchical navigation recursive query that's in one of my SQL books and post it, although that might take me a day or two if I can't find it in the next 15 minutes or so.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2015 at 8:20 pm
petervdkerk (7/28/2015)
@Lynn: this looks great already! Thanks!I now see that this groups all article groups, independent of the productid...how would I alter this query to return only the concatenated articlegroups for a single product?
Simply adding product_category_mapping to Lynn's solution should get you to the single product...
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
NULL, --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;
July 28, 2015 at 8:31 pm
That'd be awesome! I can wait now I now a solution is nearby. Great to have experts look over my shoulder at this 🙂
Thanks!
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply