Convert a wide file to a long file?

[rocketD]

Hi All,

I have a table in my database that has, for each ID number, up to 4
different types of Genus & species. For example, the fields are as
such:

ID Genus1 Species1 Genus2 Species2 Genus3 Species3
etc...
12 P. maniculatus M. sinomus
13 P. eremicus

I'd like to shift this table from wide to long, creating a many-to-many
relationship between ID and Genus/Species and giving each different
combo of ID and Genus/Species a unique ID (primary key), like so:

uniqueID ID Genus Species
1 12 P. maniculatus
2 12 M. sinomus
3 13 P. eremicus

....and so on.

I know how to do this in SAS, but I don't have access to it right now.
Does anyone have any suggestions on how to do this besides running a
number of different append queries?

Thanks,
Dara

 

[Dirk Goldgar]

Hi All,

I have a table in my database that has, for each ID number, up to 4
different types of Genus & species. For example, the fields are as
such:

ID Genus1 Species1 Genus2 Species2 Genus3 Species3
etc...
12 P. maniculatus M. sinomus
13 P. eremicus

I'd like to shift this table from wide to long, creating a
many-to-many relationship between ID and Genus/Species and giving
each different combo of ID and Genus/Species a unique ID (primary
key), like so:

uniqueID ID Genus Species
1 12 P. maniculatus
2 12 M. sinomus
3 13 P. eremicus

...and so on.

I know how to do this in SAS, but I don't have access to it right now.
Does anyone have any suggestions on how to do this besides running a
number of different append queries?

I would do it with 4 append queries -- setting that up would be faster
than writing the code to do it with one pass through a recordset.