SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 query change the column dinamically		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sebastian11c
Posting Yak Master

125 Posts
Posted - 04/27/2012 :  10:13:17  Show Profile  Reply with Quote
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

USA
15558 Posts
Posted - 04/27/2012 :  10:53:27  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

sebastian11c
Posting Yak Master

125 Posts
Posted - 04/27/2012 :  10:56:31  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15558 Posts
Posted - 04/27/2012 :  11:01:21  Show Profile  Visit robvolk's Homepage  Reply with Quote
SELECT code, year, price FROM CarPrice
UNPIVOT (price FOR year IN([2008],[2009], [2010])) b
Go to Top of Page

sebastian11c
Posting Yak Master

125 Posts
Posted - 04/27/2012 :  12:08:25  Show Profile  Reply with Quote
thank you so much for everyone
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000