July 28, 2015 at 8:52 pm
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. 😉
July 28, 2015 at 9:11 pm
Here is another option that uses "Nested Sets". The stored proc (dbo.CreateNestedSets) is based off of the code provided by Jeff Moden (Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/url])
It's just a dynamic version of that code...
-- Create a temp table to hold existing values and add Lft & Rgt columns to allows for the nested sets.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
[artikelid] INT NULL,
[id] [int] NOT NULL,
[parentid] [int] NULL,
[catlevel] INT NOT NULL,
[slug_nl] [nvarchar](50) NOT NULL,
[Lft] INT NULL,
[Rgt] INT NULL
);
INSERT #temp (artikelid,id,parentid,catlevel,slug_nl)
SELECT
pcm.artikelid,
ag.id,
ag.parentid,
ag.catlevel,
ag.slug_nl
FROM
dbo.articlegroups ag
JOIN dbo.products_category_mapping pcm
ON ag.id = pcm.articlegroup_id
UNION ALL
SELECT NULL, 0, NULL, -1, 'Temp Parrent';-- This row creates an artificial "Master Parrent" with a NULL parentid (required for the subsiquent proc)
EXEC dbo.CreateNestedSets-- The code for this proc is included as an attachment. It is based on the code provided by Jeff Moden http://www.sqlservercentral.com/articles/Hierarchy/94040/
@TableName = '#temp',
@ChildColName = 'id',
@ParentColName = 'parentid',
@LftColName = 'Lft',
@RgtColName ='Rgt',
@DeBug = 0;
DELETE #temp WHERE parentid IS NULL;--Now that the left & right bowers have been set, we are safe to delete the artificial master parent.
-- The final select query
SELECT
t1.artikelid,
STUFF((SELECT ' > ' + t2.slug_nl
FROM #temp t2
WHERE t2.Lft BETWEEN t1.Lft AND t1.Rgt
ORDER BY t2.Lft
FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 3, '') AS CategoryPath
FROM
#temp t1
WHERE
t1.catlevel = 0
The output results of the final select query...
artikelid CategoryPath
----------- -------------------------------------
2481446 taarttoppers > grappig
2481446 bruidstaart > taarttoppers > grappig
July 28, 2015 at 9:24 pm
Thanks again. I really need just SQL and not stored procedures as I want to use the SQL in my Solr data-config.xml which does not support stored procedures. My bad, I forgot to mention this.
Regarding the data, I left out some columns for better overview...I've now included all that in my initial post so you run on the exact same dataset as I do. Do you then still get only 5 rows using your first sample code?
July 28, 2015 at 9:37 pm
petervdkerk (7/28/2015)
Thanks again. I really need just SQL and not stored procedures as I want to use the SQL in my Solr data-config.xml which does not support stored procedures. My bad, I forgot to mention this.
Not a big deal... I put the code in a dynamic SP to make it easier to use myself... If you like the results, I'd urge you take a look at the referenced article. It really is some unbelievably good code. Once you've got your head wrapped around what it's doing, it would be a simple matter to modify it to fit you tables without having to reference it as a proc.
In fact... Here's the same script as before... without using the proc.
-- Create a temp table to hold existing values and add Lft & Rgt columns to allows for the nested sets.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
[artikelid] INT NULL,
[id] [int] NOT NULL,
[parentid] [int] NULL,
[catlevel] INT NOT NULL,
[slug_nl] [nvarchar](50) NOT NULL,
[Lft] INT NULL,
[Rgt] INT NULL
);
INSERT #temp (artikelid,id,parentid,catlevel,slug_nl)
SELECT
pcm.artikelid,
ag.id,
ag.parentid,
ag.catlevel,
ag.slug_nl
FROM
dbo.articlegroups ag
JOIN dbo.products_category_mapping pcm
ON ag.id = pcm.articlegroup_id
UNION ALL
SELECT NULL, 0, NULL, -1, 'Temp Parrent';-- This row creates an artificial "Master Parrent" with a NULL parentid (required for the subsiquent proc)
-- Replacing the dbo.CreateNestedSets proc with ad-hoc sql... ==================================================================================
IF OBJECT_ID('tempdb..#Hierarchy') IS NOT NULL
DROP TABLE #Hierarchy;
WITH cteBuildPath AS (
SELECT anchor.id,
anchor.parentid,
HLevel = 1,
SortPath = CAST(CAST(anchor.id AS BINARY(4)) AS VARBINARY(4000)) --Up to 1000 levels deep.
FROM #temp AS anchor
WHERE parentid IS NULL --Only the Root Node has a NULL ParentID
UNION ALL
SELECT recur.id,
recur.parentid,
HLevel = cte.HLevel + 1,
SortPath = CAST( --This does the concatenation to build SortPath
cte.SortPath + CAST(Recur.id AS BINARY(4)) AS VARBINARY(4000))
FROM
#temp AS recur WITH (TABLOCK)
JOIN cteBuildPath AS cte
ON cte.id = recur.parentid
)
SELECT
ChildID= ISNULL(sorted.id,0),
ParentID= sorted.parentid,
HLevel= ISNULL(sorted.HLevel,0),
LeftBower= ISNULL(CAST(0 AS INT),0), --Place holder
RightBower= ISNULL(CAST(0 AS INT),0), --Place holder
NodeNumber= ROW_NUMBER() OVER (ORDER BY sorted.SortPath),
NodeCount= ISNULL(CAST(0 AS INT),0), --Place holder
SortPath= ISNULL(sorted.SortPath,sorted.SortPath)
INTO #Hierarchy
FROM cteBuildPath AS sorted
OPTION (MAXRECURSION 100) --Change this IF necessary
;
--===========================================================================
-- Using the information created in the table above, create the
-- NodeCount column and the LeftBower and RightBower columns to create
-- the Nested Sets hierarchical structure.
--===========================================================================
--===== Declare a working variable to hold the result of the calculation
-- of the LeftBower so that it may be easily used to create the
-- RightBower in a single scan of the final table.
DECLARE @LeftBower INT
;
--===== Create the Nested Sets from the information available in the table
-- and in the following CTE. This uses the proprietary form of UPDATE
-- available in SQL Serrver for extra performance.
WITH n (n) AS (
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
), HTally (N) AS (
SELECT
(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1) * 4 + 1
FROM n n1, n n2, n n3
), cteCountDownlines AS
( --=== Count each occurance of ChildID in the sort path
SELECT ChildID = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT),
NodeCount = COUNT(*) --Includes current node
FROM #Hierarchy h,
HTally t
WHERE t.N BETWEEN 1 AND DATALENGTH(SortPath)
GROUP BY SUBSTRING(h.SortPath,t.N,4)
) --=== Update the NodeCount and calculate both Bowers
UPDATE h
SET @LeftBower = LeftBower = 2 * NodeNumber - HLevel,
h.NodeCount = downline.NodeCount,
h.RightBower = (downline.NodeCount - 1) * 2 + @LeftBower + 1
FROM #Hierarchy h
JOIN cteCountDownlines downline
ON h.ChildID = downline.ChildID
;
UPDATE t SET
t.Lft = h.LeftBower,
t.Rgt = h.RightBower
FROM
#temp t
JOIN #Hierarchy h
ON id = h.ChildID;
DROP TABLE #Hierarchy;
-- =============================================================================================================================================
DELETE #temp WHERE parentid IS NULL;--Now that the left & right bowers have been set, we are safe to delete the artificial master parent.
-- The final select query
SELECT
t1.artikelid,
STUFF((SELECT ' > ' + t2.slug_nl
FROM #temp t2
WHERE t2.Lft BETWEEN t1.Lft AND t1.Rgt
ORDER BY t2.Lft
FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 3, '') AS CategoryPath
FROM
#temp t1
WHERE
t1.catlevel = 0
July 28, 2015 at 9:56 pm
Cool! I ran it directly, but I get some weird results:
2481445taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > grappig > grappig > grappig > grappig > grappig > grappig
2481445bruidstaart > bruidstaart > bruidstaart > bruidstaart > bruidstaart > bruidstaart > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > grappig > grappig > grappig > grappig > grappig > grappig
2481446taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > grappig > grappig > grappig > grappig > grappig > grappig
2481446bruidstaart > bruidstaart > bruidstaart > bruidstaart > bruidstaart > bruidstaart > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > grappig > grappig > grappig > grappig > grappig > grappig
2481447feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481448feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481449taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > grappig > grappig > grappig > grappig > grappig > grappig
2481449bruidstaart > bruidstaart > bruidstaart > bruidstaart > bruidstaart > bruidstaart > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > grappig > grappig > grappig > grappig > grappig > grappig
2481450taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > grappig > grappig > grappig > grappig > grappig > grappig
2481450bruidstaart > bruidstaart > bruidstaart > bruidstaart > bruidstaart > bruidstaart > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > grappig > grappig > grappig > grappig > grappig > grappig
2481451taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > grappig > grappig > grappig > grappig > grappig > grappig
2481451bruidstaart > bruidstaart > bruidstaart > bruidstaart > bruidstaart > bruidstaart > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > grappig > grappig > grappig > grappig > grappig > grappig
2481452taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > grappig > grappig > grappig > grappig > grappig > grappig
2481452bruidstaart > bruidstaart > bruidstaart > bruidstaart > bruidstaart > bruidstaart > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > taarttoppers > grappig > grappig > grappig > grappig > grappig > grappig
2481453feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481454feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481455feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481456feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481457feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481458feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481459feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481460feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481461feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481462feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481463feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481464feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481465feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > feestartikelen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen > ballonnen
2481466accessoires > accessoires > accessoires > tiaras > tiaras > tiaras
2481467accessoires > accessoires > accessoires > tiaras > tiaras > tiaras
2481468accessoires > accessoires > accessoires > tiaras > tiaras > tiaras
July 28, 2015 at 10:24 pm
The computer is off for the night. I'll retest tomorrow with your updated test data.
July 29, 2015 at 6:38 am
I tried loading up your new test data... It's throwing errors. Please check your insert statements.
Also, as a matter of convenience and readability... Please post code inside a sql code block. It makes it far easier to discern the code from the rest of the narrative.
July 29, 2015 at 7:30 am
Ok, I did that now 🙂
Let me know if this works and if not what errors you're getting. btw: This script was generated using MSSQL Management Studio.
July 29, 2015 at 7:33 am
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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 29, 2015 at 7:59 am
Steve - Very nice solution! 🙂
sgmunson (7/29/2015)
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.
You can actually avoid this by altering the syntax of your FOR XML to the following...
FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)')
The updated syntax will also avoid any additional problems caused by the possibility of special characters within the text values themselves.
July 29, 2015 at 8:51 am
This looks awesome! 🙂
2 questions:
1. how can I narrow this query to show only the categorystring for a single product, e.g. "2481445"?
2. If I ever add more levels to my hierarchical navigation, will this still work without altering the query?
July 29, 2015 at 1:05 pm
petervdkerk (7/29/2015)
This looks awesome! 🙂2 questions:
1. how can I narrow this query to show only the categorystring for a single product, e.g. "2481445"?
2. If I ever add more levels to my hierarchical navigation, will this still work without altering the query?
1. how can I narrow this query to show only the categorystring for a single product, e.g. "2481445"?
The final query would need a filter on the field that becomes ProductID.
2. If I ever add more levels to my hierarchical navigation, will this still work without altering the query?
My query doesn't care how many levels there are.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 29, 2015 at 1:17 pm
I've updated my last posted query with Jason's update to the FOR XML PATH portion. FYI...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply