Hierarchy

Hello all,
I have the following table structure (legacy, not my design :-D ):
CREATE TABLE [dbo].[hierarchy]
([idproduct] [int] NOT NULL ,
[name] [varchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[idlevel0] [int] NOT NULL ,
[name0] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[idlevel1] [int] NULL ,
[name1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[idlevel2] [int] NULL ,
[name2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
)
ON [PRIMARY]
GO
Some data:
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
'Intermediate'1', 100, 'Intremediate2')
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
'Intermediate'1', 100, 'Intremediate2')
idproduct field: id of a product
name= name o a product
idlevel0 = id of the root hierarchy
name0 = name of the root hierarchy
idlevel1 = id of the second node in the hierarchy
name1 = name of the second node in the hierarchy
idlevel2 = id of the third node in the hierarchy
name2 = name of the third node in the hierarchy
basically, there can be only three levels in the tree but as much brances as
the number of records are.
what I want is to reformat the hierarchy this way:
nodeid / nodename/ parentid
any hints on this?
Kind regards,
TudorSorry, pressed send before i actually finished:
So, further data:
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10,
'Intermediate10', 100, 'Intremediate200')
insert into hierarchy values (1001, 'Product2', 1, 'root node', 10,
'Intermediate10', 100, 'Intremediate200')
insert into hierarchy values (1003, 'Product3', 1, 'root node', 10,
'Intermediate10', 101, 'Intremediate201')
insert into hierarchy values (1004, 'Product4', 1, 'root node', 11,
'Intermediate11', 100, 'Intremediate202')
insert into hierarchy values (1005, 'Product5', 1, 'root node', 12,
'Intermediate12', 102, 'Intremediate202')
I would like the data to be stored as this:
nodeid nodename parentid
1 rootnode
2 intermediate10 1
3 intermediate200 2
4 intermediate200 2
5 intermediate11 1
etc...
Thanks,
Tudor
"Tudor" <tudor@.hopscotch.com> wrote in message
news:eeN5qxbQGHA.1868@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I have the following table structure (legacy, not my design :-D ):
> CREATE TABLE [dbo].[hierarchy]
> ([idproduct] [int] NOT NULL ,
> [name] [varchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [idlevel0] [int] NOT NULL ,
> [name0] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [idlevel1] [int] NULL ,
> [name1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [idlevel2] [int] NULL ,
> [name2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> )
> ON [PRIMARY]
> GO
> Some data:
> insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
> 'Intermediate'1', 100, 'Intremediate2')
> insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
> 'Intermediate'1', 100, 'Intremediate2')
>
> idproduct field: id of a product
> name= name o a product
> idlevel0 = id of the root hierarchy
> name0 = name of the root hierarchy
> idlevel1 = id of the second node in the hierarchy
> name1 = name of the second node in the hierarchy
> idlevel2 = id of the third node in the hierarchy
> name2 = name of the third node in the hierarchy
> basically, there can be only three levels in the tree but as much brances
> as the number of records are.
> what I want is to reformat the hierarchy this way:
> nodeid / nodename/ parentid
> any hints on this?
> Kind regards,
> Tudor
>
>
>