x

SQL 2005 or SSIS Best Solution

Is there a way to change the fieldname "Country_Code" below using the next value in a lookup table that I have that contains all the fields (i.e., Country_Code is Field1, City is Field2, State is Field3).

So, I want to run the Insert below using Field1 from this lookup table above and then run it using Field2 = City, etc.

I would like some suggestions on best approach. I have tried SSIS as well.

 --- Return each field, along with appropriate counts 
 INSERT  INTO Tab_Report
 SELECT  'Country' AS Label,
         MF.Country_Code,
         QtyMailed = COUNT(DISTINCT MF.CouponCode),
         Active = COUNT(R1.Active),
         Inactive = COUNT(R2.Inactive),
         NonResponder = COUNT(MF.CouponCode) - COUNT(R1.Active) - COUNT(R2.Inactive) - COUNT(R3.Responder),
         Responder = COUNT(R3.Responder)
 FROM    #MailFile MF
 LEFT JOIN #Responder R1 ON MF.CouponCode = R1.CouponCode
                             AND R1.Active = 1
 LEFT JOIN #Responder R2 ON MF.CouponCode = R2.CouponCode
                             AND R2.Inactive = 1
 LEFT JOIN #Responder R3 ON MF.CouponCode = R3.CouponCode
                             AND R3.Responder = 1
 GROUP BY MF.Country_Code
more ▼

asked Oct 31, 2011 at 02:29 PM in Default

avatar image

redder
158 19 21 25

The only other way that I know of would be to use dynamic SQL. You would build the query text and extend it using the translation table you have to label each column as necessary.

I think doing this in SSIS may be the better option.

Nov 01, 2011 at 12:36 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Would an unpivot over the lookup table achieve this?

Something like the below. Note, you may need to conform the "Field" columns to the same data type.

 SELECT  'Country' AS Label,
         lk.ColumnData,
         QtyMailed = COUNT(DISTINCT MF.CouponCode),
         Active = COUNT(R1.Active),
         Inactive = COUNT(R2.Inactive),
         NonResponder = COUNT(MF.CouponCode) - COUNT(R1.Active) 
                      - COUNT(R2.Inactive) - COUNT(R3.Responder),
         Responder = COUNT(R3.Responder)
 FROM    #MailFile MF
 LEFT JOIN #Responder R1 ON MF.CouponCode = R1.CouponCode
                            AND R1.Active = 1
 LEFT JOIN #Responder R2 ON MF.CouponCode = R2.CouponCode
                            AND R2.Inactive = 1
 LEFT JOIN #Responder R3 ON MF.CouponCode = R3.CouponCode
                            AND R3.Responder = 1
 INNER JOIN (SELECT  Country_Code,
                     ColumnData,
                     ColumnName
             FROM    (SELECT Country_Code,
                             Field1,
                             Field2,
                             Field3
                      FROM   LookupTable) dta
 UNPIVOT ( ColumnData FOR ColumnName IN ([Field1], [Field2], [Field3]) ) up) lk 
 ON lk.Country_Code = MF.Country_Code
 GROUP BY MF.Country_Code
more ▼

answered Nov 01, 2011 at 05:13 AM

avatar image

philnolan
322 2 2 4

Eek! Sorry for that bad formatting. Have attempted to tidy but it's not having it :(

Nov 01, 2011 at 05:16 AM philnolan

That'll do it.

Nov 01, 2011 at 06:32 AM WilliamD

I may have mis-led you. In my lookup table, I have 1 column or field with the following values in that field. Country, City, State, etc. In the script above, I want to replace the Country field with the next value in my lookup table: City and then run the select statement. Once complete, I want to replace the City field with the next value in my lookup table: State and run the Select statement. Thanks!

Nov 01, 2011 at 08:44 AM redder

To clarify, could you provide examples of the two data sets please?

Nov 01, 2011 at 01:09 PM philnolan

Lookup Table Values Field1 is BreakField with values of: CountryCode, City

Mail File Values are: Field1 is CountryCode with values of: GR, AR Field2 is City with values of: Greek City, Argentina City Field3 is CouponCode (This is a match key).

Responder File Field1 is Active with values of 1 and 0 Field2 is Inactive with values of 1 and 0 Field is CouponCode (This is a match key).

--Once the insert is run below, I want to replace the CountryCode Field with --City from the Lookup Table and then run another Insert. INSERT INTO Staging_Tab_Report SELECT 'Country' AS Label, MF.CountryCode, QtyMailed = COUNT(DISTINCT MF.CouponCode),
Active = COUNT(R1.Active),
Inactive = COUNT(R2.Inactive),
NonResponder = COUNT(MF.CouponCode) - COUNT(R1.Active) - COUNT(R2.Inactive) FROM #MailFile MF LEFT JOIN #Responder R1
ON MF.CouponCode = R1.CouponCode AND R1.Active = 1 LEFT JOIN #Responder R2
ON MF.CouponCode = R2.CouponCode AND R2.Inactive = 1 GROUP BY MF.CountryCode

Thanks!

Nov 01, 2011 at 02:32 PM redder
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2031
x1090

asked: Oct 31, 2011 at 02:29 PM

Seen: 968 times

Last Updated: Oct 31, 2011 at 11:54 PM

Copyright 2017 Redgate Software. Privacy Policy