Help for query.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help for query..

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-10-17 : 01:54:21

I have category table.. as below

catid---- catname parentcatid
1 ------ Cat1 ------ 0
2 ------ Cat2 ------ 0
3 ------ Cat3 ------ 0
4 ------ Cat1_sub1 -- 1
5 ------ Cat1_sub2 -- 1
6 ------ Cat1sub_sub - 4
7 ------ Cat2_sub1 --- 2
8 ------ Cat3_sub1 --- 3
9 ------ Cat2_subsub -- 7

this is simple category table with its category id and parent category id upto NTH LEVEL.

I want LEVEL column With Same order by for Each category and its sub categories... as below.

catid catname parentcatid --- levels
1 --- Cat1 0 --- 0
4 --- Cat1_sub1 1 --- 1
6 --- Cat1sub_sub 4 --- 2
10 --- Cat1subsub_sub 6 --- 3
5 --- Cat1_sub2 1 --- 1
2 --- Cat2 0 --- 0
7 --- Cat2_sub1 2 --- 1
9 --- Cat2_subsub 7 --- 2
3 --- Cat3 0 --- 0
8 --- Cat3_sub1 3 --- 1

In above output levels column with ordered output which shows first category and its sub level categories...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-17 : 02:24:16

[code]
DECLARE @sample TABLE
(
catid int,
catname varchar(15),
parentcatid int
)
INSERT INTO @sample
SELECT 1 , 'Cat1' , 0 UNION ALL
SELECT 2 , 'Cat2' , 0 UNION ALL
SELECT 3 , 'Cat3' , 0 UNION ALL
SELECT 4 , 'Cat1_sub1' , 1 UNION ALL
SELECT 5 , 'Cat1_sub2' , 1 UNION ALL
SELECT 6 , 'Cat1sub_sub' , 4 UNION ALL
SELECT 7 , 'Cat2_sub1' , 2 UNION ALL
SELECT 8 , 'Cat3_sub1' , 3 UNION ALL
SELECT 9 , 'Cat2_subsub' , 7 UNION ALL
SELECT 10 , 'Cat2_subsub' , 6

; WITH cte
AS
(
SELECT catid, catname, parentcatid, levels = 0
FROM @sample
WHERE parentcatid = 0

UNION ALL

SELECT s.catid, s.catname, s.parentcatid, levels = c.levels + 1
FROM cte c
INNER JOIN @sample s ON c.catid = s.parentcatid
)
SELECT *
FROM cte
/*
catid catname parentcatid levels
----------- --------------- ----------- -----------
1 Cat1 0 0
2 Cat2 0 0
3 Cat3 0 0
8 Cat3_sub1 3 1
7 Cat2_sub1 2 1
9 Cat2_subsub 7 2
4 Cat1_sub1 1 1
5 Cat1_sub2 1 1
6 Cat1sub_sub 4 2
10 Cat2_subsub 6 3

(10 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-10-17 : 02:39:17

Thanks
but i need the same way output by level and its sub category

cat1 level 0
then its sub cat1's sub and its level 1
then cat1's sub's sub category its level 2

cat2 level 0
then its sub.. level 1

and so on...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-17 : 02:42:08

you mean the ordering of the result ?

KH
[spoiler]Time is always against us[/spoiler]

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-10-17 : 02:45:38

Yes khtan..

as below which i have shown...
here below..
level 0 and its cat1
level 1 with cat1 's sub
level 3 and cat's sub 's sub.. nth LEVEL

catid catname parentcatid --- levels
1 --- Cat1 0 --- 0
4 --- Cat1_sub1 1 --- 1
6 --- Cat1sub_sub 4 --- 2
10 --- Cat1subsub_sub 6 --- 3
5 --- Cat1_sub2 1 --- 1
2 --- Cat2 0 --- 0
7 --- Cat2_sub1 2 --- 1
9 --- Cat2_subsub 7 --- 2
3 --- Cat3 0 --- 0
8 --- Cat3_sub1 3 --- 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-17 : 02:46:16

[code]

; WITH cte
AS
(
SELECT catid, catname, parentcatid, levels = 0, lvl = RIGHT('000' + CONVERT(varchar(MAX), catid), 3)
FROM @sample
WHERE parentcatid = 0

UNION ALL

SELECT s.catid, s.catname, s.parentcatid, levels = c.levels + 1, lvl = c.lvl + RIGHT('000' + CONVERT(varchar(3), s.catid), 3)
FROM cte c
INNER JOIN @sample s ON c.catid = s.parentcatid
)
SELECT catid, catname, parentcatid, levels
FROM cte
ORDER BY lvl
[/code]

KH
[spoiler]Time is always against us[/spoiler]

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-10-17 : 02:54:08

THANKS A LOT...

i have previously tried out this same query using cursor.. but this time i am trying with different way..

Thanks again..

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-10-17 : 06:23:18

Hello khtan..
i have 1 question that is..
when i put varchar (50) and removing varchar(MAX)
it gives error like...

"Types don't match between the anchor and the recursive part in column "

both have same datatype varchar still...
and if i put varchar(MAX) it goes successfully..

?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources