Vlookup in SQL - SQL Server Forums

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

Vlookup in SQL

Author

Topic

Concatch
Starting Member

4 Posts

Posted - 2011-05-23 : 08:52:38

Hi,

I'm using Microsoft SQL 2008 R2. In excel I have approx 2000 unique rows with a unique name eg 'SMS REVENUE'. There are 12 columns per row representing months and there is data in each cell.

In a separate sheet I have selected specific rows and have used the vlookup function to go through all the cells from the original sheet and only pull those relating to the new rows and columns and insert the values.

My formula is as follows =VLOOKUP($A88,'budget 2011'!$A$1:$O$2187,O$2,FALSE) which works perfectly. I need to do the same thing in SQL. I've imported the the 'budget 2011' sheet into SQL and this acts as my staging table. I've created another table with the following script I will import into a software programme:

'with cell ZBZ.Contract[InterconnectRevenue]!!<Time.Month('$$$'), TimePeriod.Total,OffNetOperator.Total,RevenueStream.RevenueStream('UsageRevenue'),Scenario.Scenario('Budget')> set value ###;

I have created a formula for each ### in a column that is made up of the unique row names. eg SMSRevenue-DataRevenue.

What I need to do is vlookup the values relating to these specific names that can be found in the budget 2011 table and insert them into the new table replacing the ### but for all 12 months. The $$$ represent the month.

This info needs to be placed into a new table. The idea is to automate the process eventually.

Thanks a million!!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-23 : 09:06:53

A vlookup would be done using an INNER JOIN. The values in $A88 would have to be common to both tables,O$2 would be the name of the columns whose values you want. Something like
SELECT b.wantedValue
FROM [budget 2011] b
INNER JOIN SpecificName s on b.$A88 = s.$A88

You'd have to replace everything with actual names, of course.

Jim

Everyday I learn something that somebody else already knew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-05-23 : 10:06:04

Actually VLOOKUP is more of a outer join query because when there is no match, the original record is still visible.

N 56°04'39.26"
E 12°55'05.63"

dhen21dx
Starting Member

4 Posts

Posted - 2013-10-25 : 08:43:22

Yeah, is there a query for outer join?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 08:48:52

quote:
Originally posted by dhen21dx

Yeah, is there a query for outer join?

yes
LEFT,RIGHT or FULL OUTER JOIN based on your requirement

http://www.w3schools.com/sql/sql_join.asp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-25 : 09:25:44

OUTER APPLY would be the most similar.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 09:26:40

quote:
Originally posted by SwePeso

OUTER APPLY would be the most similar.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Yes
as explained here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=189135

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources