If I join two tables I will get the below table
SELECT a.Category, a.categoryid, b.parentid,b.Level
FROM a JOIN b ONa.CatId=b.Catid
Actual Data:-
Category Catid ParentID Level
-----------------------------------------------------------
Pumps & Accss 20 0 0
Inground Pumps 213 20 1
Above Ground 215 20 1
Commercial Pumps 216 20 1
Hawrd super pumps 814 213 2
Hywrd north pumps 815 213 2
This is an example one using cursor I have to apply like this for all Category Id's...
I need to display this data like this:
Cat1 Cat2 Cat3 ItemNo
------------------------------------------------------------
Pumps & Accss Above Ground Hawrd super AX007123
Pumps & Accss Above Ground Hywrd north AX0071201
Pumps & Accss Commercial Pu Hawrd super AX007754
Pumps & Accss Commercial Pu Hawrd super AX0077891
Pumps & Accss Inground Pumps Hywrd north AX0071251
Level 1 in Cat1 column and Level2 in Cat2 column like this i
need to get the output. like this I have 10 levels kindly post some good answer to resolve this.
check this... you can use multiple time the same table to build a multi level join
declare @category table(categoryId int, category char(100), principalId int)
declare @article table(articleId int, article varchar(100), categoryId int)
insert into @category(categoryId, category, principalId)
values (1,'One', null)
insert into @category(categoryId, category, principalId)
values (2,'two', null)
insert into @category(categoryId, category, principalId)
values (11,'eleven', 1)
insert into @category(categoryId, category, principalId)
values (21,'twenty one', 2)
insert into @category(categoryId, category, principalId)
values (22,'twenty two', 2)
insert into @category(categoryId, category, principalId)
values (111,'one hundred eleven', 11)
insert into @category(categoryId, category, principalId)
values (211,'two hundred eleven', 21)
insert into @category(categoryId, category, principalId)
values (221,'two hundred twenty one', 22)
insert into @category(categoryId, category, principalId)
values (2211,'two thousand two hundred twenty one', 221)
insert into @article(articleId, article, categoryId)
values (1, 'Article 1', 111)
insert into @article(articleId, article, categoryId)
values (2, 'Article 2', 211)
insert into @article(articleId, article, categoryId)
values (3, 'Article 3', 221)
insert into @article(articleId, article, categoryId)
values (4, 'Article 4', 2211)
select coalesce(c5.category,'') c5,
coalesce(c4.category,'') c4,
coalesce(c3.category,'') c3,
coalesce(c2.category,'') c2,
coalesce(c1.category,'') c1,
a.article
from @article a
left join @category c1 on c1.categoryId = a.categoryId
left join @category c2 on c2.categoryId = c1.principalId
left join @category c3 on c3.categoryId = c2.principalId
left join @category c4 on c4.categoryId = c3.principalId
left join @category c5 on c5.categoryId = c4.principalId
if you share the tables definition it helps to improve my answer, hope it help yo to point in the right direction