hierarchical output

I have a Table with 2 columns 1)Parent column-ASSEMBLY_ID and 2)child
column-COMPONENT_ID
I want the output as a hierarchy given below. Any idea on how to get the
output?(I am using a Stored Procedure on SQL Server 2000 to generate the
output)
CREATE TABLE [dbo].[IPDS_MBOM_INTERFACE]
(
[ASSEMBLY_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[COMPONENT_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
----
---
INSERT INTO IPDS_MBOM_INTERFACE (ASSEMBLY_ID, COMPONENT_ID)
VALUES ('320192 - 3', 'AS12438791')
INSERT INTO
IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
VALUES ('320192 - 3', 'AS12438792')
INSERT INTO
IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
VALUES ('AS12438792', 'AS12438793')
INSERT INTO
IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
VALUES ('AS12438793', 'AS12438794')
INSERT INTO
IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
VALUES ('AS12438794', 'AS12438795')
INSERT INTO
IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
VALUES ('AS12438794', 'AS12438796')
----
---
EXPECTED OUTPUT WITH HEADINGS
ASSEMBLY_ID LEVEL1 LEVEL2 LEVEL3
LEVEL4
320192 - 3 AS12438791
320192 - 3 AS12438792 AS12438793 AS12438794
AS12438795
320192 - 3 AS12438792 AS12438793 AS12438794
AS12438796http://www.google.com/url?sa=D&q=ht..._qd_14_5yk3.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Nishanth" <cvnishanth@.hotmail.com> schrieb im Newsbeitrag
news:eCZfUHrWFHA.2796@.TK2MSFTNGP09.phx.gbl...
>I have a Table with 2 columns 1)Parent column-ASSEMBLY_ID and 2)child
> column-COMPONENT_ID
> I want the output as a hierarchy given below. Any idea on how to get the
> output?(I am using a Stored Procedure on SQL Server 2000 to generate the
> output)
> CREATE TABLE [dbo].[IPDS_MBOM_INTERFACE]
> (
> [ASSEMBLY_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [COMPONENT_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> )
> ----
--
> ---
> INSERT INTO IPDS_MBOM_INTERFACE (ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('320192 - 3', 'AS12438791')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('320192 - 3', 'AS12438792')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438792', 'AS12438793')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438793', 'AS12438794')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438794', 'AS12438795')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438794', 'AS12438796')
> ----
--
> ---
> EXPECTED OUTPUT WITH HEADINGS
> ASSEMBLY_ID LEVEL1 LEVEL2 LEVEL3
> LEVEL4
> 320192 - 3 AS12438791
> 320192 - 3 AS12438792 AS12438793 AS12438794
> AS12438795
> 320192 - 3 AS12438792 AS12438793 AS12438794
> AS12438796
>
>|||Try:
SELECT
a.assembly_id,
a.component_id AS level1,
b.component_id AS level2,
c.component_id AS level3,
d.component_id AS level4
FROM #ipds_mbom_interface a
LEFT JOIN #ipds_mbom_interface b ON a.component_id = b.assembly_id
LEFT JOIN #ipds_mbom_interface c ON b.component_id = c.assembly_id
LEFT JOIN #ipds_mbom_interface d ON c.component_id = d.assembly_id
WHERE NOT EXISTS
(
-- Record is root parent, ie has children but no parent ids above it
SELECT *
FROM #ipds_mbom_interface
WHERE component_id = a.assembly_id
)
This query works for your sample data, you'll need to make sure it works for
your real data!
Let me know how you get on.
Damien
"Nishanth" wrote:

> I have a Table with 2 columns 1)Parent column-ASSEMBLY_ID and 2)child
> column-COMPONENT_ID
> I want the output as a hierarchy given below. Any idea on how to get the
> output?(I am using a Stored Procedure on SQL Server 2000 to generate the
> output)
> CREATE TABLE [dbo].[IPDS_MBOM_INTERFACE]
> (
> [ASSEMBLY_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [COMPONENT_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> ----
--
> ---
> INSERT INTO IPDS_MBOM_INTERFACE (ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('320192 - 3', 'AS12438791')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('320192 - 3', 'AS12438792')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438792', 'AS12438793')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438793', 'AS12438794')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438794', 'AS12438795')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438794', 'AS12438796')
> ----
--
> ---
> EXPECTED OUTPUT WITH HEADINGS
> ASSEMBLY_ID LEVEL1 LEVEL2 LEVEL3
> LEVEL4
> 320192 - 3 AS12438791
> 320192 - 3 AS12438792 AS12438793 AS12438794
> AS12438795
> 320192 - 3 AS12438792 AS12438793 AS12438794
> AS12438796
>
>|||Damien,
The problem is the data that I had sent is for 4 levels
but in reality I will have 'n' levels so the query you have sent may not
suit my needs completely.
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:D8C8A4BF-71BF-4440-A32A-DC52A27CFF8D@.microsoft.com...
> Try:
> SELECT
> a.assembly_id,
> a.component_id AS level1,
> b.component_id AS level2,
> c.component_id AS level3,
> d.component_id AS level4
> FROM #ipds_mbom_interface a
> LEFT JOIN #ipds_mbom_interface b ON a.component_id = b.assembly_id
> LEFT JOIN #ipds_mbom_interface c ON b.component_id = c.assembly_id
> LEFT JOIN #ipds_mbom_interface d ON c.component_id = d.assembly_id
> WHERE NOT EXISTS
> (
> -- Record is root parent, ie has children but no parent ids above it
> SELECT *
> FROM #ipds_mbom_interface
> WHERE component_id = a.assembly_id
> )
> This query works for your sample data, you'll need to make sure it works
for
> your real data!
> Let me know how you get on.
>
> Damien
> "Nishanth" wrote:
>
NULL
NULL
> ----
--
> ----
--|||Get a copy of TRESS & HIERARCHIES IN SQL. UYouc an use the nested sets
model and do this in one query without any proprietary code.|||I'm sure the others will tell me off for doing this, but try the following
script:
DECLARE @.select VARCHAR( 8000 )
DECLARE @.from VARCHAR( 8000 )
DECLARE @.where VARCHAR( 8000 )
DECLARE @.alias1 VARCHAR( 3 )
DECLARE @.alias2 VARCHAR( 3 )
DECLARE @.i TINYINT
-- Initialise
SET @.i = 1
SET @.alias1 = 1 -- will be used for table aliases
SET @.alias2 = @.alias1 + 1
SET @.select =
'SELECT
t1.assembly_id,
t1.component_id AS level1,
'
SET @.from =
'FROM #ipds_mbom_interface t1'
-- Add required levels
WHILE @.i < 99
BEGIN
-- Build SELECT clause
SET @.select = @.select + SPACE(4) +
't' + @.alias2 + '.component_id AS level' + CONVERT( VARCHAR(2), @.i + 1 )
+ ', ' + CHAR( 10 )
-- Build FROM clause
SET @.from = @.from + CHAR( 10 ) + SPACE(4) +
'LEFT JOIN #ipds_mbom_interface t' + @.alias2 + ' ON t' + @.alias1 +
'.component_id = t' + @.alias2 + '.assembly_id'
-- Increment variables
SET @.i = @.i + 1
SET @.alias1 = @.alias1 + 1
SET @.alias2 = @.alias1 + 1
END
-- Trim last comma and trailing space
SET @.select = SUBSTRING( @.select, 1, LEN( @.select ) - 3 ) + CHAR(10)
-- Add WHERE clause
SET @.where =
'
WHERE NOT EXISTS
(
-- Record is root assembly_id, ie has component_idren but no assembly_id
ids above it
SELECT *
FROM #ipds_mbom_interface
WHERE component_id = t1.assembly_id
)
'
-- Print the dynamic SQL
-- PRINT @.select + @.from + @.where
-- Execute the dynamic SQL
EXEC( @.select + @.from + @.where )
Practically, you shouldn't really be doing something like this for so many
reasons, but let me know how you get on.
Damien
"Nishanth" wrote:

> Damien,
> The problem is the data that I had sent is for 4 levels
> but in reality I will have 'n' levels so the query you have sent may not
> suit my needs completely.
> "Damien" <Damien@.discussions.microsoft.com> wrote in message
> news:D8C8A4BF-71BF-4440-A32A-DC52A27CFF8D@.microsoft.com...
> for
> NULL
> NULL
> --
> --
>
>