Issues in Lookup Transformation

Question

Post reply

Issues in Lookup Transformation

bhushan.bagul

Mr or Mrs. 500

Points: 553
More actions
October 11, 2010 at 5:32 am

#147276

I want to get column values depending on column data type and can use that values to insert in destination table.
For e.g. I have a lookup SQL where multiple records come for phone type (home, business, mobile) and in my destination table I have three columns like Home_phone, Business_phone and Mobile_phone. Now in my main SQL I have a colummn customer_key and also in lookup SQL. How can I get values for these Home_phone, Business_phone and Mobile_phone columns from lookup SQL using Customer_key?
Please let me know if you guys need more information for the same.
Thanks!
Bhushan

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

A join on these two tables would be more elegant based on Customer_key:-):-):-)

Raunak J

bhushan.bagul Mr or Mrs. 500 Points: 553 More actions · Answer 2

Thanks for your reply! But that will not solve my problem because in my main SQL I have only one record whereas in lookup SQL I have 3 records for phone types. An I don't want to insert duplicate records with different phone types in destination table thats why I have my destination table with 3 separate column for each phone type.

Did you get this?

Thanks!

Bhushan

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

Please provide some sample data to help us help you and what all till date have you tried?

Raunak J

bhushan.bagul Mr or Mrs. 500 Points: 553 More actions · Answer 4

OLE DB Source SQL:

Select customer_key From customer (nolock)

customer_key

-------------

10001

Lookup SQL:

Select customer_key, phone_type, phone_number

From phone (nolock) as ph Join customer (nolock) as c

On ph.customer_key = c.customer_key

customer_key phone_type phone_number

---------------------------------------------

10001 Home 1234

10001 Business 5678

10001 Mobile 918239

Destination Table DDL:

Cust_Phone_Info

---------------------------------------------------------

customer_key home_phone business_phone mobile_phone

---------------------------------------------------------

10001 1234 5678 918239

This what I am expecting. Now if I join these 2 SQL then result will be three rows and I don't want to store in that way in my destination table.

I hope this will clarify what I needed.

Thanks!

Bhushan

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 5

bhushan.bagul (10/11/2010)
OLE DB Source SQL:
Select customer_key From customer (nolock)
customer_key
-------------
10001
Lookup SQL:
Select customer_key, phone_type, phone_number
From phone (nolock) as ph Join customer (nolock) as c
On ph.customer_key = c.customer_key
customer_key phone_type phone_number
---------------------------------------------
10001 Home 1234
10001 Business 5678
10001 Mobile 918239
Destination Table DDL:
Cust_Phone_Info
---------------------------------------------------------
customer_key home_phone business_phone mobile_phone
---------------------------------------------------------
10001 1234 5678 918239
This what I am expecting. Now if I join these 2 SQL then result will be three rows and I don't want to store in that way in my destination table.
I hope this will clarify what I needed.
Thanks!
Bhushan

Bhushan,

My approach:

Step 1: Use a pivot transformation for your Table 2 and transform it into as below:

{Key,Phone 1,Phone 2,Phone 3 }

Step 2: Use Join

Raunak J

bhushan.bagul Mr or Mrs. 500 Points: 553 More actions · Answer 6

Seems this Pivot will work for me. Thanks for you reply!

steveb. SSC-Forever Points: 46733 More actions · Answer 7

Or if you want a pure SSIS solution you could opt for 3 sperate lookups

one for each for home, business, mobile

and then add each of these as a seperate column.

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 8

Raunak Jhawar

SSCoach

Points: 15701

October 11, 2010 at 7:16 am

#1234960

Happy to help. :-):-):-)

Raunak J

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

The pure T-SQL solution:

SELECT

customer_key= c.customer_key

,home_phone= h.phone_number

,business_phone= b.phone_number

,mobile_phone= m.phone_number

FROMcustomer c

LEFT OUTER JOIN

phoneh

ONc.customer_key= h.customer_key

ANDh.phone_type= 'Home'

LEFT OUTER JOIN

phoneb

ONc.customer_key= b.customer_key

ANDb.phone_type= 'Business'

LEFT OUTER JOIN

phonem

ONc.customer_key= m.customer_key

ANDm.phone_type= 'Mobile'

Beware, as this solution introduces NULL values for customers that don't have a phone number for a certain phone type.

I used LEFT OUTER JOINS, because it is possible for a customer to have a mobile phone and a home phone, but not a business phone, for example.

If a customer has multiple phone numbers for a specific phone type, you will still get multiple rows (e.g. a family having multiple mobile phone numbers). How are you planning to incorporate that in your solution?

A small side question: why do you use the nolock hint everywhere in you SQL statements? Is there a specific reason for?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 10

Nice...still wondering which is better join or pivot?:-D

Raunak J

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 11

Raunak Jhawar (10/11/2010)
Nice...still wondering which is better join or pivot?:-D

The join, as the pivot transformation is semi-blocking 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 12

Nice...still wondering which is better join or 3 lookup task in SSIS.:-)

Raunak J

prabhu.st SSCrazy Points: 2552 More actions · Answer 13

Try out a Execute SQL Task with the following code

create table #temp (cust_id int,phone_type varchar(10),phone_number int)

insert into #temp values(10001,'Home',1234),(10001,'Business',5678),(10001,'Mobile',918239)

selectcust_id,

max((case when phone_type='Home' then phone_number else 0 end)) as home_phone,

max((case when phone_type='Business' then phone_number else 0 end)) as Business_phone,

max((case when phone_type='Mobile' then phone_number else 0 end)) as Mobile_phone

from #temp

group by cust_id

--drop table #temp