November 29, 2014 at 2:02 am
Dear all
Following is my db table
student_id student_code student_parent_id student_name
1 11 NULL a
2 111 1 b
3 1111 2 c
4 11111 3 d
I want to generate following op
student_id student_code student_parent_id student_name Hierarchy
1 11 0 a 11
2 111 1 b 11-111
3 1111 2 c 11-111-1111
4 11111 3 d 11-111-1111-11111
I am concatenating the hierarchy with other fields, its giving me the following error..
Types don't match between the anchor and the recursive part in column "Hierarchy" of recursive query
--- create table
create table test(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))
---- insert records
insert into test values (1, '11', '', 'a')
insert into test values (2, '111', 1, 'b')
insert into test values (3, '1111', 2, 'c')
insert into test values (4, '11111', 3, 'd')
---- result query
;WITH SInfo AS
(
SELECT sId
,scode
,ParentId
,sName
--,CONVERT(nvarchar(800), scode) AS Hierarchy
CONVERT(nvarchar(800), scode) + '+' + 'sName' AS Hierarchy
FROM test
WHERE ParentId = 0
UNION ALL
SELECT TH.sId
,TH.scode
,TH.ParentId
,TH.sName
,CONVERT(nvarchar(800), (SInfo.Hierarchy +'\' + CONVERT(nvarchar(800), TH.scode)))
FROM test TH
INNER JOIN SInfo ON SInfo.sId = TH.ParentId
)
Select * from SInfo
thanks
nick
November 29, 2014 at 2:20 am
Quick suggestion, place the concatenation inside the convert function. You can use the "describe first result set" procedure to examine the difference.
😎
November 29, 2014 at 8:37 am
peterausger (11/29/2014)
Dear all
Following is my db table
student_id student_code student_parent_id student_name
1 11 NULL a
2 111 1 b
3 1111 2 c
4 11111 3 d
I want to generate following op
student_id student_code student_parent_id student_name Hierarchy
1 11 0 a 11
2 111 1 b 11-111
3 1111 2 c 11-111-1111
4 11111 3 d 11-111-1111-11111
I am concatenating the hierarchy with other fields, its giving me the following error..
Types don't match between the anchor and the recursive part in column "Hierarchy" of recursive query
There's a bit of confusion between what you posted above and the code that you posted so I gave you a couple of working examples that you could modify to suite your needs.
/* CAREFUL!!! THIS SNIPPET DROPS THE TEST TABLE!!!!
--===== Conditionally drop the test table to make reruns easier in SSMS
IF OBJECT_ID('dbo.test','U') IS NOT NULL
DROP TABLE dbo.test
;
*/
GO
--===== Create the test table
CREATE TABLE dbo.test
(
sid BIGINT
,scode NVARCHAR(50)
,parentid BIGINT
,sname NVARCHAR(50)
)
;
--===== Populate the test table with test data
INSERT INTO dbo.test
(sid, scode, parentid, sname)
SELECT 1, '11' , NULL, 'a' UNION ALL
SELECT 2, '111' , 1 , 'b' UNION ALL
SELECT 3, '1111' , 2 , 'c' UNION ALL
SELECT 4, '11111', 3 , 'd'
;
--===== Display the hierarchy
-- This one only uses the SCode.
WITH SInfo AS
(
SELECT [sId]
,scode
,ParentId
,sName
,Hierarchy = CONVERT(NVARCHAR(800), scode)
FROM dbo.test
WHERE ParentId IS NULL
UNION ALL
SELECT TH.sId
,TH.scode
,TH.ParentId
,TH.sName
,Hierarchy = CONVERT(NVARCHAR(800), cte.Hierarchy+'\'+TH.scode)
FROM dbo.test TH
JOIN SInfo cte ON cte.sId = TH.ParentId
)
SELECT *
FROM SInfo
ORDER BY Hierarchy
;
--===== Display the hierarchy
-- This one uses the SCode and the SName
WITH SInfo AS
(
SELECT [sId]
,scode
,ParentId
,sName
,Hierarchy = CONVERT(NVARCHAR(800),scode+'+'+sname)
FROM dbo.test
WHERE ParentId IS NULL
UNION ALL
SELECT TH.sId
,TH.scode
,TH.ParentId
,TH.sName
,Hierarchy = CONVERT(NVARCHAR(800), cte.Hierarchy+'\'+TH.scode+'+'+th.sname)
FROM dbo.test TH
JOIN SInfo cte ON cte.sId = TH.ParentId
)
SELECT *
FROM SInfo
ORDER BY Hierarchy
;
Here are the run results from the code above...
sId scode ParentId sName Hierarchy
--- ----- -------- ----- -----------------
1 11 NULL a 11
2 111 1 b 11\111
3 1111 2 c 11\111\1111
4 11111 3 d 11\111\1111\11111
(4 row(s) affected)
sId scode ParentId sName Hierarchy
--- ----- -------- ----- -------------------------
1 11 NULL a 11+a
2 111 1 b 11+a\111+b
3 1111 2 c 11+a\111+b\1111+c
4 11111 3 d 11+a\111+b\1111+c\11111+d
(4 row(s) affected)
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2014 at 8:54 am
BTW, although this is kind of a "set based" recursive CTE (rCTE from here on) because it processes a whole level in the hierarchy at a time, rCTEs can be a bit resource intensive, a bit slow, and are somewhat limited in what kinds of queries you can write.
My recommendation would be to convert this to NESTED SETS. Just a couple of warnings to go along with that recommendation... 1) If you use the traditional "push stack" method to do the conversion, that will also be quite slow. 2) If you destroy the Adjacency List (parent/child table, like what you have now) as most do when they make the conversion, maintenance of the hierarchy will become much more difficult.
Instead, consider the methods demonstrated in the following article where you'll have the best of all worlds but one.
[font="Arial Black"]Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/font][/url]
If you need to aggregate things like scores, attendance, grouped headcounts, etc, etc, then consider taking it one step further using a similar method demonstrated in the following article.
[font="Arial Black"]Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations[/font][/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply