Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Issues in Lookup Transformation Expand / Collapse
Author
Message
Posted Monday, October 11, 2010 5:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 26, 2013 6:30 AM
Points: 25, Visits: 80

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
Post #1002074
Posted Monday, October 11, 2010 5:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
A join on these two tables would be more elegant based on Customer_key

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #1002088
Posted Monday, October 11, 2010 6:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 26, 2013 6:30 AM
Points: 25, Visits: 80
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
Post #1002095
Posted Monday, October 11, 2010 6:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
Please provide some sample data to help us help you and what all till date have you tried?

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #1002098
Posted Monday, October 11, 2010 6:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 26, 2013 6:30 AM
Points: 25, Visits: 80
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
Post #1002107
Posted Monday, October 11, 2010 6:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
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


Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #1002126
Posted Monday, October 11, 2010 7:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 26, 2013 6:30 AM
Points: 25, Visits: 80
Seems this Pivot will work for me. Thanks for you reply!
Post #1002137
Posted Monday, October 11, 2010 7:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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.

Post #1002139
Posted Monday, October 11, 2010 7:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
Happy to help.

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #1002142
Posted Monday, October 11, 2010 7:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,252, Visits: 10,135
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
FROM customer c
LEFT OUTER JOIN
phone h
ON c.customer_key = h.customer_key
AND h.phone_type = 'Home'
LEFT OUTER JOIN
phone b
ON c.customer_key = b.customer_key
AND b.phone_type = 'Business'
LEFT OUTER JOIN
phone m
ON c.customer_key = m.customer_key
AND m.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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1002159
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse