Gustav Brock
gustav at cactus.dk
Tue May 11 14:30:30 CDT 2004
Hi Mark
How about
<SQL>
Select
Left(Code, 1) As L1,
Left(Code, 3) As L2,
Left(Code, 5) As L3,
Left(Code, 7) As L4,
Left(Code, 9) As L5
From
tblCodes;
</SQL>
It looks too simple, but according to your specs ...
/gustav
> Sorry if this has been done before but my heads killing and I can't
> figure it at all. Using AXP etc. I have a list of codes, some of which
> are parents and some children (going down 5 levels). What I want is 5
> columns (1 for each level), each containing not only the codes for that
> level, but all the codes from the higher levels...
> E.g.
> Code Parent
> 1
> 1.1 1
> 1.2 1
> 1.3 1
> 1.2.1 1.2
> 1.2.2 1.2
> 1.1.1 1.1
> 1.1.1.1 1.1.1
> 1.1.1.1.1 1.1.1.1
> Etc. becomes something like
> L1 L2 L3 L4 L5
> 1 1 1 1 1
> 1 1.1 1.1 1.1 1.1
> 1 1.2 1.2.1 1.2.1 1.2.1
> This is for dumping into a pivot in Excel and so I can roll totals up to
> selected levels, i.e. each record has a column for each level etc. Some
> code structures don't go down 5 levels but I still need to show a value
> in level 5 etc.
> The problem is column 5 must be unique but in some cases (i.e.) where
> the codes are less than 5 levels deep) I get duplicates.
> Sorry if all this sounds a bit odd... It is :@) I am trying to avoid
> temp tables and code as I'm trying to keep it all data driven as
> (hopefully) this is going to fit an existing reporting tool which is
> driven primarily by saved SQL strings...
> Any ideas ???
> Cheers
> Mark