Difficult Query 1

[shannonp]

I would like to produce a query that takes columns of data and sorta crosstabs it.

For example, I have a table with the following column headings:

TREENUM, HT01avg, HT01stdev, HT01min, HT01max, WD01avg, WD0102stdev, WD02min, WD02max (note that HT01=Height and WD01=Width)

I would like an SQL solution to crosstab it so it has the following Column Headings:

TREENUM, *TRAIT, avg, stdev, min, max
*TRAIT in this case will equal the front part of the column name (in this case, HT01 and WD01)

I have NO IDEA where to start and unfortunately am restricted to using the latter format as a base table.

Any help would of course be most appreciated.

Cheers

Shannon

 

[r937]

you said HT01 or WD01, but then you have columns called WD0102stdev, WD02min, WD02max?

assuming those are typos...

[tt]select TREENUM
, 'HT01' as TRAIT
, HT01avg as avg
, HT01stdev as stdev
, HT01min as min
, HT01max as max
from yourtable
union all
select TREENUM
, 'WD01'
, WD01avg
, WD01stdev
, WD01min
, WD01max
from yourtable[/tt]


rudy

http://r937.com/

 

[shannonp]

Awesome...thanks Rudy. The difficulty now is to make a complete VBA solution rather than saving it as a query ~ have to because of the amount of Columns I'm actually dealing with, but I'm sure it will be kaotically manageable. hehe...

Cheers
Shannon