January 4, 2012 at 6:16 am
AD7 (1/4/2012)
This looks like hierarchical or "tree structured" data.The following query can be used to extract hierarchical or "tree structured" data.
/** setup data **/
declare @table table (ID int, PID int, Name varchar(100))
insert into @table values (1,0,'Ad')
insert into @table values (2,0,'Area')
insert into @table values (3,1,'List')
insert into @table values (4,1,'Detail')
insert into @table values (5,2,'List')
insert into @table values (6,2,'Detail');
--select * from @table;
/** recursive tree query **/
with Tree (ID, PID, Name, TreeLevel, ConcatName)
as
(
select
ID,
PID,
Name,
TreeLevel = 1,
ConcatName = cast(Name as varchar(100))
from
@table
where
PID = 0
union all
select
t.ID,
t.PID,
t.Name,
TreeLevel = Tree.TreeLevel + 1,
ConcatName = cast((Tree.name + ' ' + t.Name) as varchar(100))
from
@table t
inner join
Tree on t.PID = Tree.ID
)
select
*
from
Tree
where
TreeLevel > 1
order by
ConcatName asc
oh this worked for me better but only one issue..i want to explain onething more..actually i want to get menu item.mean Area is menue and its child item are list and details....i do not want to concate them.......how it could be..i need parent items too....like area have 0 parent id ..
January 4, 2012 at 6:22 am
DROP TABLE #Sample
CREATE TABLE #Sample (ID INT, PID INT, Name VARCHAR(10))
INSERT INTO #Sample (ID, PID, Name)
SELECT 1, 0, 'Ad' UNION ALL
SELECT 2, 0, 'Area' UNION ALL
SELECT 3, 1, 'List' UNION ALL
SELECT 4, 1, 'Detail' UNION ALL
SELECT 5, 2, 'List' UNION ALL
SELECT 6, 2, 'Detail'
-- 2 levels:
SELECT s.Name, r.Name
FROM #Sample s
INNER JOIN #Sample r ON r.PID = s.ID
WHERE s.PID = 0 AND r.PID <> 0
ORDER BY s.PID
-- more than 2 levels:
;WITH rCTE AS (
SELECT Level = 1, ID, PID, Name
FROM #Sample
WHERE PID = 0
UNION ALL
SELECT Level = Level + 1, tr.ID, tr.PID, tr.Name
FROM rCTE lr
INNER JOIN #Sample tr ON tr.PID = lr.ID
)
SELECT s.Name, r.Name
FROM #Sample s
INNER JOIN rCTE r ON r.PID = s.ID
WHERE s.PID = 0 AND r.PID <> 0
ORDER BY s.PID
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 4, 2012 at 6:33 am
Just exclude the where clause in the last query to extract the parent items as well:
select
*
from
Tree
--where
--TreeLevel > 1
order by
ConcatName asc
January 5, 2012 at 1:25 am
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply