May 7, 2009 at 6:59 am
Hi,
I have a problem with t sql and i don't know how i can do !
here is it my two table
table famille
familleId primarey key
famille varchar
table sousFamille
sousFamilleId primarey key
sousFamille varchar
familleId foreign key
I have some data in these two table :
example :
famille:
-------
1 |famille 1
2 |famille 2
sous famille
-----------
3 |sous famille1 | 1
4 |sous famille2 | 2
5| sous famille x| 2
and i d like to have this result on a query
famille 1
famille 1 / sous famille 1
famille 2
famille 2 / sous famille 2
famille 2 / sous famille 2/sous famille x
I 'm lost have you got any idea ?
Thanks for all
Christophe
May 7, 2009 at 7:39 am
Try this, see if it does what you need:
set nocount on;
if object_id(N'tempdb..#famille') is not null
drop table #famille;
if object_id(N'tempdb..#sousFamille') is not null
drop table #sousFamille;
--
create table #famille (
familleID int primary key,
famille varchar(100));
--
create table #sousFamille (
sousFamilleID int primary key,
sousFamille varchar(100),
familleID int);
--
insert into #famille (familleID, famille)
select 1, 'famille 1' union all
select 2, 'famille 2';
--
insert into #sousFamille (sousFamilleID, sousFamille, familleID)
select 3, 'sous famille1', 1 union all
select 4, 'sous famille2', 2 union all
select 5, 'sous famille x', 2;
--
;with CTE as
(select familleID, famille as parent, famille, 1 as prime
from #famille
union all
select #sousFamille.familleID, famille, sousFamille, 2 as prime
from #sousFamille
inner join #famille
on #sousFamille.familleID = #famille.familleID),
CTE2 as
(select row_number() over (partition by familleID order by prime, famille) as row,
familleID, famille, parent
from CTE)
select parent + isnull(
(select '/' + famille
from CTE2 CTE2a
where familleID = CTE2.familleID
and row <= CTE2.row
and famille != CTE2.parent
for XML path(''), type).value('.','varchar(max)'), '')
from CTE2;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply