InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 11:30:40 GMT20http://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspxNice...still wondering which is better join or 3 lookup task in SSIS.:-)Mon, 11 Oct 2010 07:56:21 GMTRaunak Jhawarhttp://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspx[quote][b]Raunak Jhawar (10/11/2010)[/b][hr]Nice...still wondering which is better join or pivot?:-D[/quote]The join, as the pivot transformation is semi-blocking :-)Mon, 11 Oct 2010 07:50:51 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspxNice...still wondering which is better join or pivot?:-DMon, 11 Oct 2010 07:39:47 GMTRaunak Jhawarhttp://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspxThe pure T-SQL solution:[code="sql"]SELECT customer_key = c.customer_key ,home_phone = h.phone_number ,business_phone = b.phone_number ,mobile_phone = m.phone_numberFROM 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' [/code]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?Mon, 11 Oct 2010 07:34:48 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspxHappy to help. :-):-):-)Mon, 11 Oct 2010 07:16:24 GMTRaunak Jhawarhttp://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspxOr if you want a pure SSIS solution you could opt for 3 sperate lookupsone for each for home, business, mobileand then add each of these as a seperate column.Mon, 11 Oct 2010 07:09:41 GMTsteveb. http://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspxSeems this Pivot will work for me. Thanks for you reply!Mon, 11 Oct 2010 07:05:46 GMTbhushan.bagulhttp://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspx[quote][b]bhushan.bagul (10/11/2010)[/b][hr]OLE DB Source SQL:Select customer_key From customer (nolock)customer_key-------------10001Lookup SQL:Select customer_key, phone_type, phone_numberFrom phone (nolock) as ph Join customer (nolock) as cOn ph.customer_key = c.customer_keycustomer_key phone_type phone_number---------------------------------------------10001 Home 123410001 Business 567810001 Mobile 918239Destination Table DDL:Cust_Phone_Info---------------------------------------------------------customer_key home_phone business_phone mobile_phone---------------------------------------------------------10001 1234 5678 918239This 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[/quote]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 JoinMon, 11 Oct 2010 06:51:47 GMTRaunak Jhawarhttp://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspxOLE DB Source SQL:Select customer_key From customer (nolock)customer_key-------------10001Lookup SQL:Select customer_key, phone_type, phone_numberFrom phone (nolock) as ph Join customer (nolock) as cOn ph.customer_key = c.customer_keycustomer_key phone_type phone_number---------------------------------------------10001 Home 123410001 Business 567810001 Mobile 918239Destination Table DDL:Cust_Phone_Info---------------------------------------------------------customer_key home_phone business_phone mobile_phone---------------------------------------------------------10001 1234 5678 918239This 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!BhushanMon, 11 Oct 2010 06:27:43 GMTbhushan.bagulhttp://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspxPlease provide some sample data to help us help you and what all till date have you tried?Mon, 11 Oct 2010 06:10:13 GMTRaunak Jhawarhttp://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspxThanks 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!BhushanMon, 11 Oct 2010 06:07:19 GMTbhushan.bagulhttp://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspxA join on these two tables would be more elegant based on Customer_key:-):-):-)Mon, 11 Oct 2010 05:50:16 GMTRaunak Jhawarhttp://www.sqlservercentral.com/Forums/Topic1002074-148-1.aspxI 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!BhushanMon, 11 Oct 2010 05:32:57 GMTbhushan.bagul