Select a Column depending on a row value

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

General SQL Server Forums

New to SQL Server Programming

Select a Column depending on a row value

Author

Topic

glpita
Starting Member

17 Posts

Posted - 2009-09-23 : 16:45:49

Hi all,

I have 2 databases, DB1 & DB2. DB1 lists orders made at certain companies:

Order_id | Zip_Code| Comp_Name |
----------------------
000001 | 21211 | Comp#1 |
000002 | 21217 | Comp#4 |
000003 | 21210 | Comp#7 |
000004 | 32329 | Comp#2 |
.....

DB2 has prices of the same product for several companies at several zipcodes and looks like:

ZipCode | Comp#1 | Comp#2 | Comp#3 | Comp#4 | Comp#...
-------------------------------------------------------
21210 | $10.00 | $9.95 | $12.00 | $10.50 | ....
21215 | $11.00 | $8.95 | $11.50 | $11.00 | ....
...

How can I do so that the query looks for the proper COLUMN in DB2 based in the ROW values of Comp_Name in DB1?

Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-25 : 13:58:00

If sql 2005 do like this:-


USE [DB1]
GO
SELECT t.Order_id,t.ZipCode,t.Comp_Name,t1.Price 
FROM YourTable t
INNER JOIN(SELECT ZipCode,Comp_Name,Price
           FROM DB2..YourTable
           UNPIVOT (Price FOR Comp_Name IN ([Comp#1],[Comp#2],[Comp#3],[Comp#4]))u)t1
ON t1.ZipCode=t.ZipCode
AND t1.Comp_Name=t.Comp_Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources