[AccessD] changing data from "Crosstab" to "Normal" tbl view?

Gustav,

as the north germans say "alte Schwede!" :-O

tried it on a small table..... it works!
kewl :-)

but it also means i need a union for every col.......... 30 weeks 
don't know if its practical with 40k datasets, have to give it a try,

thanx a lot for the sql though, till now I've never seen/had any real use for a union.

all the best :-)
alun


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Thursday, January 22, 2004 5:54 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] changing data from "Crosstab" to "Normal" tbl
view?


Hi Alun

Quoting Alexei Kartaschov from 2001-10-05:

<quote>

Suppose your crosstab table is tblCT
with fields Key, A, B, C, D...

1. Pure but uninspired.
SELECT Key, "A" AS Letter, A AS MyData FROM tblCT
UNION
SELECT Key, "B", B FROM tblCT
...
UNION
SELECT Key, "Z", Z FROM tblCT

</quote>

/gustav


> I have a tbl that looks like a spreedsheet, the table looks like this:

> ART_NR  W1      W2      W3      W4      .... W30
> 110             10      8       12      14
> 258             20      16      22      13

> what I want to do is "normalize" the data like this:

> ART_NR  WEEK            ORDERS
> 110             1               10
> 110             2               8
> 110             3               12
> 110             4               14
> 258             1               20
> 258             2               16
> etc

> is that possible (with sql), I can't think of anything :-(

> alun

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com