November 3, 2011 at 5:06 am
I have a table of customer updates that I first need to pivot in order to give me one row per customer with all the updates. This is first done with a pivot transformation, but the output of the pivot trsnafornation looks like the following :
customer_id new_address new_city
1234567 [null] anycity
1234567 the new street, 8 [null]
I would like to aggregate these lines to have one line per customer, but the aggregation transformation does not ignore the nulls...... Is there some way I can work around this, or is there a setting somewhere that I am missing?
Thx for the help!
November 3, 2011 at 7:36 am
You may be able to use the ISNULL function to just clean the data temporarily before pivoting:
http://msdn.microsoft.com/en-us/library/ms184325.aspx
_________________________________
seth delconte
http://sqlkeys.com
November 4, 2011 at 6:41 am
How are you doing your pivot transformation?
you could just select the following from your results.
select
customer_id,
max(new_address) as new_address,
max(new_city) as new_city
from [yourtable]
group by
customer_id
November 7, 2011 at 5:56 am
I tried the pivot transformation in two ways, initially with a query using a case statement, then using the pivot transformation in SSIS. Both produced the same result.
I'll look at putting the results into a staging table and then using the select query that you suggested.
I'll keep you posted!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply