Crosstab query

Can someone show me how to write a query to convert this:

Year, Account, Qtr1, Qtr2, Qtr3, Qtr4
2004, 12345, 100, 200, 300, 400
2005, 23456, 200, 300, 400, 500

to this:

Year, Account, Quarters, Amount
2004, 12345, Qtr1, 100
2004, 12345, Qtr2, 200
2004, 12345, Qtr3, 300
2004, 12345, Qtr4, 400
2005, 23456, Qtr1, 200
2005, 23456, Qtr2, 300
2005, 23456, Qtr3, 400
2005, 23456, Qtr4, 500

So far I've only found examples which convert values to columns but not
for columns to values. TIA... ALHi

You are looking to unpivot your table such as
http://www.umachandar.com/technical...ipts/Main25.htm

Although you will probably doing this one the client.

John

<albundy1998@.hotmail.com> wrote in message
news:1115058028.710708.183670@.f14g2000cwb.googlegr oups.com...
> Can someone show me how to write a query to convert this:
> Year, Account, Qtr1, Qtr2, Qtr3, Qtr4
> 2004, 12345, 100, 200, 300, 400
> 2005, 23456, 200, 300, 400, 500
> to this:
> Year, Account, Quarters, Amount
> 2004, 12345, Qtr1, 100
> 2004, 12345, Qtr2, 200
> 2004, 12345, Qtr3, 300
> 2004, 12345, Qtr4, 400
> 2005, 23456, Qtr1, 200
> 2005, 23456, Qtr2, 300
> 2005, 23456, Qtr3, 400
> 2005, 23456, Qtr4, 500
> So far I've only found examples which convert values to columns but not
> for columns to values. TIA... AL|||albundy1...@.hotmail.com wrote:
> Can someone show me how to write a query to convert this:
> Year, Account, Qtr1, Qtr2, Qtr3, Qtr4
> 2004, 12345, 100, 200, 300, 400
> 2005, 23456, 200, 300, 400, 500
> to this:
> Year, Account, Quarters, Amount
> 2004, 12345, Qtr1, 100
> 2004, 12345, Qtr2, 200
> 2004, 12345, Qtr3, 300
> 2004, 12345, Qtr4, 400
> 2005, 23456, Qtr1, 200
> 2005, 23456, Qtr2, 300
> 2005, 23456, Qtr3, 400
> 2005, 23456, Qtr4, 500
> So far I've only found examples which convert values to columns but
not
> for columns to values. TIA... AL

One simple way is via unions:
select Year, Account, 'Qty1' AS Quarters, Qtr1 AS Amount
from acctqtrs
union
select Year, Account, 'Qty2' AS Quarters, Qtr2 AS Amount
from acctqtrs
...

--
David Rowland
How well is your server performing
http://dbmonitor.tripod.com
-- New release of DBMonitor is almost ready with new functionality