August 17, 2011 at 4:06 am
declare @table table
(
PK int,
Parent int,
Level int,
Codeint
)
insert into @table
select1, 0, 0, 100union all
select2, 1, 1, 101union all
select3, 1, 1, 102union all
select4, 2, 2, 103union all
select5, 3, 2, 104union all
select6, 4, 3, 105
selectt.PK, t.Parent, t.Level, Value = t.Code,
[P1 Code] = max(p1.Code),
[P2 Code] = max(p2.Code),
[P3 Code] = max(p3.Code)
from@table t
left join @table p1
ont.Parent= p1.PK
left join @table p2
onp1.Parent= p2.PK
left join @table p3
onp2.Parent= p3.PK
group by t.PK, t.Parent, t.Level, t.Code
i have a table and i want my parent id to be displayed only onece
and rest to its related rows in another table it should be
displayed as null. it will be like a tree structure
i want output to look like:
a.id a.name b.num
1 abc 101
null null 102
null null 103
2 bcd 101
null null 102
I want the parent to be displayed only once and from next row it should show
null but the child in #t1details table whould be displayed.
August 17, 2011 at 4:12 am
http://msdn.microsoft.com/en-us/magazine/cc794278.aspx
In SQL Server 2008 you can now store data in hierarchies , the above link shows how.
August 17, 2011 at 4:25 am
Could you please clarify what you want top be displayed as: a.id a.name b.num
There are no such columns in your sample table setup.
But as general answer: most likely you can use recursive CTE to achieve what you want.
August 17, 2011 at 7:57 am
From looking at your sample you might want to look into nested sets. Do a search on google for nested sets and you should find plenty of examples. It is a really great way to handle this kind of thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 17, 2011 at 8:15 am
It's duplicated thread from TSQL(2005) forum...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply