Bill of material

I have a table with data
create table a
(
a01 char(4),
a02 char(4),
a03 int
);
insert into a
values('a','b',1);
insert into a
values('a','c',1);
insert into a
values('x','c',1);
insert into a
values('c','g',1);
insert into a
values('b','h',1);
insert into a
values('h','k',1);
insert into a
values('b','k',1);
insert into a
values('g','k',1);
if my entry where value "k", i want show result
a01,a03
a 2
x 1
how to do ....
-
thanks!Get a copy of TREES & HIERARCHIES IN SQL for help.|||thanks a lot!
this my tree sql parent to find child ,but i want child to find parent,
do you have any idea?
ALTER PROCEDURE BOMIA_PPart
(
@.MODE NVARCHAR(12)
)
AS
SET NOCOUNT ON
CREATE TABLE #TEMP_A
(
LVL INT,
PARENT NVARCHAR(12) ,
PARTNO NVARCHAR(12) ,
M CHAR(2),
P CHAR(2),
PARTDESC NVARCHAR(40) ,
UNIT NVARCHAR(3) ,
USAGE REAL,
LOCATION NVARCHAR(18) ,
SXRQ NVARCHAR(10),
SSRQ NVARCHAR(10)
)
DECLARE @.ENDTREE INT
DECLARE @.NLVL INT
SELECT @.ENDTREE = 0
SELECT @.NLVL=1
INSERT INTO #TEMP_A(LVL,PARENT,PARTNO,P,USAGE,LOCATI
ON,SXRQ,SSRQ)
SELECT 1,IB001 ,IB003 ,IB031,IB004,IB011,IB008,IB009
FROM BOMIB
WHERE IB001=@.MODE
WHILE (@.ENDTREE =0 )
BEGIN
SELECT @.NLVL=@.NLVL+1
INSERT INTO #TEMP_A(LVL,PARENT,PARTNO,P,USAGE,LOCATI
ON,SXRQ,SSRQ)
SELECT @.NLVL,
A.IB001 ,
A.IB003 ,
A.IB031,
A.IB004,
A.IB011,
A.IB008,
A.IB009
FROM BOMIB A, #TEMP_A B
WHERE A.IB001 =B.PARTNO
AND B.LVL=@.NLVL -1
AND B.P!='1'
IF NOT EXISTS (SELECT PARTNO COLLATE database_default FROM #TEMP_A WHERE
LVL =@.NLVL)
SELECT @.ENDTREE=1
END
UPDATE #TEMP_A
SET M=AA070,
PARTDESC=AA020,
UNIT=AA050
FROM INVAA,#TEMP_A
WHERE AA010=PARTNO
SELECT
CAST(REPLICATE('.',LVL)+CAST(LVL AS NVARCHAR(2)) AS CHAR(12)) AS ',
PARENT AS ',
PARTNO AS ',
M,
CASE WHEN P='1' THEN '-'
WHEN P='2' THEN '+'
WHEN P='3' THEN '*'
ELSE '.'
END AS P,
PARTDESC AS ',
UNIT AS ',
ROUND(USAGE,2) AS ',
SXRQ AS ',
SSRQ AS ',
LOCATION AS '
FROM #TEMP_A
DROP TABLE #TEMP_A
SET NOCOUNT OFF
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1121909782.095848.154870@.g14g2000cwa.googlegroups.com...
> Get a copy of TREES & HIERARCHIES IN SQL for help.
>|||Did you bother to follow up my posting? TREES & HIERARCIES IN SQL
might be good read before you post again .sql