create new table from base table

Hi,
I am trying to create a new table from an existing base table. The base
table isn't normalised in any way. The table was given to me and that's
what I have to work with. The problem is that parent ids can have
multiple sectors and the second level records only have 1 sector. I
want to iteratate to the parent record and take all the sector
available and put them in the new table together with the alfacode.
base table:
id, name, parent, alfacode, level, sector
eg.
1, test, 0, 100, 0, 122
1, test, 0, 100, 0, 123
2, test1, 1, 101, 1, 122
3, test2, 1, 102, 1, 122
destination table:
alfacode, sector
The destination table with the data above would become:
100, 122
100, 123
101, 122
101, 123
102, 122
102, 123
Hope I made myself clear.
Any help would be greatly appreciated.
PezkelHi Pezkel
Try something like:
SELECT a.alfacode, a.sector
FROM #structure a
WHERE a.level = 0
UNION
SELECT b.alfacode, b.sector
FROM #structure a
JOIN #structure b ON A.id = b.parent
UNION
SELECT b.alfacode, a.sector
FROM #structure a
JOIN #structure b ON A.id = b.parent
John
"barcode@.dds.nl" wrote:

> Hi,
> I am trying to create a new table from an existing base table. The base
> table isn't normalised in any way. The table was given to me and that's
> what I have to work with. The problem is that parent ids can have
> multiple sectors and the second level records only have 1 sector. I
> want to iteratate to the parent record and take all the sector
> available and put them in the new table together with the alfacode.
> base table:
> id, name, parent, alfacode, level, sector
> eg.
> 1, test, 0, 100, 0, 122
> 1, test, 0, 100, 0, 123
> 2, test1, 1, 101, 1, 122
> 3, test2, 1, 102, 1, 122
> destination table:
> alfacode, sector
> The destination table with the data above would become:
> 100, 122
> 100, 123
> 101, 122
> 101, 123
> 102, 122
> 102, 123
> Hope I made myself clear.
> Any help would be greatly appreciated.
> Pezkel
>|||Hi John,
Thanks. I will give it a spin tomorrow.|||Hi John,
Just ran the query and it works like a charm. Very much appreciated.