transpose data using sql

[bombler]

I would like to turn a normalised table in to an unnormalised table in the simplest possible mannor...

I have:

col1 col2
----------
A 1
A 2
A 3

and would like to output in one line:

A 1 2 3

Would like this in MSAccess97 but am open to other sql tools.

Regards,
Brent

 

[BillDHS]

There is no easy way to change rows to columns, you need a sequence number for the max number of rows the value in col1 can have. The sequence should restart each time col1 value changes.

Select Col1,
MAX(Case When SeqCol = seq1 then col2 else 0 end),
MAX(Case When SeqCol = seq2 then col2 else 0 end),
MAX(Case When SeqCol = seq3 then col2 else 0 end),
...
MAX(Case When SeqCol2 = seq then col2 else 0 end)
From Tab1
Group By col1;

If you have a small number of values for A, it isn't too bad, but the case statments must cover the highest number. The MAX on each case statment will put all A values on one row.

MS Excel handles Xtabs very well, then the data could be reimported back into MSAccess.

 

[JonFer]

Access97 has a crosstab query type which does this for you. They are easy to set up.