Putting Column Values into Column Headings

[hewett_nick]

My apologies if this has been answered elsewhere - being a complete SQL
newbie I don't know what to look for.

I have a table that looks something like this...

ID Type Qty
1 Apples 2
1 Oranges 4
1 Pears 2
2 Apples 1
2 Oranges
2 Pears 5
3 Nuts 3
3 Raisins 100
3 Seeds 99
3 Things 6
4 Apples 3
4 Oranges
4 Pears

I need an SQL statement that will transpose the above to a table like
this...

ID Apples Oranges Pears Nuts Raisins Seeds Things
1 2 4 2
2 1 5
3 3 100 99 6
4 3

The order of the columns is not important. I'll then need to
JOIN?/UNION? this to another table that shares the ID number.

Any help, even if its just pointers to a useful resource, would be
greatly appreciated.

Cheers, NickH

 

[John Spencer]

Take a look at crosstab queries.

You will
-- group by ID and set crosstab to row heading
-- group by Type and set crosstab to Column heading
-- Sum qty and set crosstab to Value

If you start a query from the query wizard, one of the options is a crosstab
query. The wizard should lead you throught the process

 

[hewett_nick]

Many thanks John.