470,618 Members | 1,723 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,618 developers. It's quick & easy.

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... AL

Jul 23 '05 #1
2 1543
Hi
You are looking to unpivot your table such as
http://www.umachandar.com/technical/...pts/Main25.htm

Although you will probably doing this one the client.

John
<al*********@hotmail.com> wrote in message
news:11**********************@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

Jul 23 '05 #2

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

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Nathan Bloomfield | last post: by
2 posts views Thread by Sherman H. | last post: by
4 posts views Thread by Judy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.