recursive query with counter for father and child

Thursday, July 3, 2014

I have one t-sql table stueckli and program next query


with liste as (SELECT Artikelnum AS vater, Stueckarti AS kind, anzahl, 0 AS LEVEL, cast(Artikelnum + '-' + cast(e.Stueckarti AS varchar(100)) AS varchar(100)) AS level1, xaktuell FROM dbo.STUECKLI AS e WHERE xaktuell = '1‘


UNION ALL


SELECT e.Artikelnum AS vater, e.Stueckarti AS kind, e.anzahl, LEVEL + 1, cast(level1 + '-' + cast(e.Stueckarti AS varchar(100)) AS varchar(100)), e.xaktuell FROM dbo.STUECKLI AS e INNER JOIN liste AS l ON e.Artikelnum = l.kind and e.xaktuell=1) SELECT * from liste


RESULT: 000_253_059_LL 000_254_500_H 1,000 0 000_253_059_LL-000_254_500_H 000_253_059_LL 000_253_144_BD 1,000 0 000_253_059_LL-000_253_144_BD 000_254_500_H 111_253_060_CB 1,000 1 000_253_059_LL-000_254_500_H-111_253_060_CB 000_254_500_H 222_253_675_A 1,000 1 000_253_059_LL-000_254_500_H-222_253_675_A 000_254_500_H 004_253_675_B 1,000 1 000_253_059_LL-000_254_500_H-004_253_675_B 111_253_060_CB 000_131_690_FM 1,000 2 000_253_059_LL-000_254_500_H-111_253_060_CB-000_131_690_FM 111_253_060_CB 000_253_100_C 1,000 2 000_253_059_LL-000_254_500_H-111_253_060_CB-000_253_100_C


Perfect!


But now i want to put a counter within it. For Example 000_253_059_LL is the vater and 000_254_500_H is the child. I want to put two Fields one with the counter of the child and one with of the all the vaters. So the Result should be: Count of „head“ 000_253_059_LL is 100


101 100_101 000_253_059_LL 000_254_500_H 1,000 0 000_253_059_LL-000_254_500_H 102 100_102 000_253_059_LL 000_253_144_BD 1,000 0 000_253_059_LL-000_253_144_BD 103 100_101_103 000_254_500_H 111_253_060_CB 1,000 1 000_253_059_LL-000_254_500_H-111_253_060_CB 104 100_101_104 000_254_500_H 222_253_675_A 1,000 1 000_253_059_LL-000_254_500_H-222_253_675_A 104 100_101_104 111_253_060_CB 000_253_100_C 1,000 2 000_253_059_LL-000_254_500_H-111_253_060_CB-000_253_100_C


I







http://ift.tt/1rmQOoL