One alternative to the unpivot is to use the Cross apply method.

DECLARE @t table (Col1 char(25), col2 char(25),col3 char(25))

INSERT INTO @t (col1, col2, col3) VALUES

('AB' , 'BC' ,'DA')

SELECT

Cav.Value

,CASE Value

WHEN...

@pietlinden, It just shows there are multiple ways of accomplishing the same task, and to be pragmatic, as in some circumstances one solution may perform slightly better than...

I thought with the additional filter on the correlated sub query it would act as an exclusive filter, for that one row, but I'm happy to be wrong.

pietlinden (11/11/2014)

---

Correlated subqueries are your friend...

select top 1000 lead_id, activity_type_code, activity_date,

row_number() over(partition by lead_id order by activity_date desc) rownum

from [PartitionExample] pe

WHERE EXISTS (SELECT 1 FROM PartitionExample WHERE activity_type_code =...

Right, First question to consider is what is unique about all set that has the row with a value of 'M'

This should work

SELECT

*

FROM PartitionExample OuterPartition

JOIN

(SELECT Lead_ID

FROM PartitionExample InnerPartition

WHERE...

as I've said...

Unfortunately...

Personally I don't see the issue with allowing scripting tools to access a production server, especially if its for validation purposes.

Assuming the database isn't huge why not just back...

You might find this article quite interesting as well,http://www.sqlservercentral.com/articles/T-SQL/63681/ as it also shows the performance of the PIVOT and the more traditional Cross Tab method, especially over larger data sets.

The Out of Memory Exception is likely to be Client side and the Temporary FILE that SSMS uses hitting the max limits, especially with all the results being returned.

I wouldn't...

One minor issue with your code you don't seem to be incrementing the @I counter so it will run indefincately (possibly a cut/paste error)

The problem you will have is that...

Very Odd, did you recreate the connection?

The only other thing could be that it was using the wrong driver, Or the connection file had become corrupt.

Knowing SSIS and...

Sorry I think I misunderstood, are you running it through an SQL Agent job or in the development Environment?

These are always difficult (frustrating and fun at the same time) to find and diagnose, and could be any number of issues, but these tend to be the main ones...

lduvall (10/2/2014)

---

Thanks for all the suggestions. I think I'm going to use the Import/Export wizard to create the initial package & then go in and modify each step to include...