table row values for column headers

5 pts.
Tags:
SQL
SQL Database
SQL tables
Hi. I have two tables in a simple database: Prices ID (int, identity, PK) Year (smallint - year of value) Quarter (smallint - year quarter 1-4) Area (smallint - FK to Areas.ID) Index (float) Areas ID (int, identity, PK) Name (varchar) So, as you can see, the Prices table contains price information over time for certain areas. This might look like this: Prices ID Year Quarter Area Index 1 2000 1 1 100 2 2000 2 1 110 3 2000 3 1 112 4 2000 4 1 115 5 2000 1 2 200 6 2000 2 2 210 7 2000 3 2 212 8 2000 4 2 215 Areas ID Name 1 North 2 South Now, what I would like to have returned from a query is the following: Year Quarter North South 2000 1 100 200 2000 2 110 210 2000 3 112 212 2000 4 115 215 so, in other words, the row names from areas become column names. I'm sure this must be possible. Could someone point me in the correct direction? Thanks!
ASKED: March 17, 2009  7:25 PM
UPDATED: March 19, 2009  9:45 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you are on 2005+, <b><i>one option</i></b> could be to use the PIVOT operator.

The following query should provide the desired output <b><i>in this particular case, assuming that there is a fixed number of areas, and only one row exists for each year-quarter-area combination</i></b> (because it needs an aggregation function, and a SUM function is being used)

<pre>SELECT year,quarter, [North], [South]
FROM
(
SELECT p.year,p.quarter,p.index_,a.name
FROM prices p JOIN areas a
ON p.area = a.id) t
PIVOT
(
SUM(index_)
FOR name IN ([North], [South])
) pvt</pre>

————————————

If you are on 2000, instead you can use:

SELECT b.Year, b.Quarter,c.[index] AS north, b.[index] AS south
FROM Areas a
INNER JOIN Prices b ON a.ID = b.Area AND a.name = ‘south’
INNER JOIN Prices c ON c.year = b.year and c.quarter=b.quarter
INNER JOIN Areas d ON c.area=d.ID and d.name=’north’

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following