query change the column dinamically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query change the column dinamically

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-04-27 : 10:13:17

hi there

i have a problem

i have 2 tables and i need to create a query

my tables

car info

make linecode year (first row have name columns)
mazda 354 2010
bmw 467 2008

and my other table

car prices in (usd)

code 2008 2009 2010 (first row have name columns)
354 23000 25000 28000 (usd)
467 35000 39000 43000 (usd)

how could i relate the two tables,if depending on the year the car would have to relate to a different column

this is the result that i need

make line year price
mazda 354 2010 28000

thanks in advanced

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-27 : 10:53:27

The better option is to store the car price data like this:

Code Year Price
354  2008 23000 
354  2008 25000 
354  2008 28000 
467  2008 35000 
467  2009 39000 
467  2010 43000

Then your query is a simple JOIN:

SELECT C.make, P.*
FROM CarInfo C
INNER JOIN CarPrices P ON C.LineCode=P.Code AND C.Year=P.Year

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-04-27 : 10:56:31

if there are no other solution,, how could i transfer my info

from
code 2008 2009 2010 (first row have name columns)
354 23000 25000 28000 (usd)
467 35000 39000 43000 (usd)

to

Code Year Price
354 2008 23000
354 2008 25000
354 2008 28000
467 2008 35000
467 2009 39000
467 2010 43000

thanks in advanced

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-27 : 11:01:21

SELECT code, year, price FROM CarPrice
UNPIVOT (price FOR year IN([2008],[2009], [2010])) b

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-04-27 : 12:08:25

thank you so much for everyone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources