converting square matrix to columns

[E.Hobson]

I have a square matrix of values that I'd like to re-format into a few
columns in order to better manipulate them. I was wondering if anyone could
help me with this.

My data matrix is essentially a pairwise comparison of similarity values
between individuals (example below). The square matrix format is the text
output from a program used to assess similarity, and is the only format
choice. I would like to change it so that instead of the individuals' names
as column headings, I would like to have 3 columns total: Individual1,
Individual2, and similarity value (between that specific pair of
individuals).

Current format:
IndivA IndivB IndivC (etc.)
IndivA 1.0 0.5 0.3
IndivB 0.5 1.0 0.7
IndivC 0.3 0.7 1.0

Would like to convert to 3 columns:
Indiv1 Indiv2 Similarity value
A A 1.0
A B 0.5
A C 0.3
B A 0.5
(etc.)

I would like to do this kind of set up because I have several similarity
measurements per person that I would like to manipulate in several ways (ex:
average all similarity values between a pair to get average pair similarity,
etc.)

I would be very grateful for any input on this problem. Thank you for your
help!

 

[KARL DEWEY]

Use a union query. You did not name the first field (column).
SELECT [first field ] AS [Indiv1], "A" AS [Indiv2], IndivA AS [Similarity
value]
FROM [YourTable]
UNION All SELECT [first field ] AS [Indiv1], "B" AS [Indiv2], IndivB AS
[Similarity value]
FROM [YourTable]
UNION All SELECT [first field ] AS [Indiv1], "C" AS [Indiv2], IndivC AS
[Similarity value]
FROM [YourTable];

 

[E.Hobson]

Thanks for your quick response! I was wondering if there is any other way to
do this - I have over 100 individuals in one data set from one analysis.
Each dataset is run through several analyses, and I was hoping to find a
shorter way to manage the data. It is sort of like the output of a crosstab
query - is there any way to easily reverse that format?

Thanks again for any help anyone can provide,

E. Hobson