PIVOT Transformation or use something else?
http://www.rad.pasfu.com/index.php?/archives/14-PIVOT-Transformation-SSIS-Complete-Tutorial.html Source Query OLEDB ProductID Name OrderDate DueDate ShipDate 879 All-Purpose Bike Stand 1/07/2003 null null 879 All-Purpose Bike Stand null 1/08/2003 null 879 All-Purpose Bike Stand null null 29/07/2003 wish to end up with WITH A SINGLE RECORD ProductID Name OrderDate DueDate ShipDate 879 All-Purpose Bike Stand 1/07/2003 1/08/2003 29/07/2003 Im not sure Pivot transformation is way to go--I wish to end up as shown above with a single record that captures each of the dates per ProductID--any other dataflow component I could try thanks in advance Dave
November 17th, 2010 11:11pm

In OLEDB Source use this: Select ProductId,PName,OrderDate from SampleProduct Where OrderDate Is Not Null Then take the green op of the source to a lookup component. The lookup query would be: Select ProductId,DueDate from SampleProduct Where DueDate Is Not Null. Do a join based on ProductId and select the Duedate as lookup column Similarly use another lookup and let it consume the green (matching) output of the previous lookup component. The lookup query should be: Select ProductId,ShipDate from SampleProduct Where ShipDate Is Not Null. Do a join based on ProductId and select the Shipdate as lookup column Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
November 17th, 2010 11:53pm

Thanks Nitesh--its a really really complex SQL Source Query-and I cant break it down to simple terms like above--Im stuck with the source query given to me and can only process the result set --lets expand the result set slightly -look closely at the dates i end up with and note they are the maximum dates if there are more than one or nulls if there are no dates (see ProductID 880) Im using Adeventure Works data here just as a sample--but my real life scenario is dealing with millions of incoming records with a source query written by a DBA Source Query result ProductID Name OrderDate DueDate ShipDate 879 All-Purpose Bike Stand 1/07/2003 null null 879 All-Purpose Bike Stand null 1/08/2003 null 879 All-Purpose Bike Stand null 2/08/2003 null 879 All-Purpose Bike Stand null null 29/07/2003 879 All-Purpose Bike Stand null null 30/07/2003 879 All-Purpose Bike Stand null null 30/07/2003 880 Bikethree wheel null null null Here is what I would need to end up with ProductID Name OrderDate DueDate ShipDate 879 All-Purpose Bike Stand 1/07/2003 2/08/2003 30/07/2003 880 Bikethree wheel null null null
November 18th, 2010 3:01am

Hi Dave, Generally, aggregation calculations such as GROUP BY and SUM, these are typically calculated faster using Transact-SQL instead of in-memory calculations by a pipeline. So is that possible doing such calculation in T-SQL level? You may need help from T-SQL forum. Anyway, here is a sample: CREATE TABLE SSISFroum (ProductID INT, Name VARCHAR(100), OrderDate DATETIME, DueDate DATETIME, ShipDate DATETIME) INSERT INTO SSISFroum VALUES (879, 'All-Purpose Bike Stand' , '20030702',null,null ), (879 , 'All-Purpose Bike Stand' , null,'20030801',null ), (879 , 'All-Purpose Bike Stand' , null,'20030802',null ), (879 , 'All-Purpose Bike Stand' , null,null,'20030729'), (879 , 'All-Purpose Bike Stand' , null,null,'20030730'), (880, 'Bikethree wheel' ,null , null, null) SELECT ProductID, Name, MAX(OrderDate) AS OrderDate, MAX(DueDate) AS DueDate, MAX(ShipDate) AS ShipDate FROM SSISFroum GROUP BY ProductID, Name -------output ProductID Name OrderDate DueDate ShipDate 879 All-Purpose Bike Stand 2003-07-02 00:00:00.000 2003-08-02 00:00:00.000 2003-07-30 00:00:00.000 880 Bikethree wheel NULL NULL NULL Hope this helps, Raymond Raymond Li - MSFT
Free Windows Admin Tool Kit Click here and download it now
November 18th, 2010 3:19am

Raymond is right, this case is not PIVOT case, you have all columns available in the results, just you need to group by them and apply an aggregation.http://www.rad.pasfu.com
November 18th, 2010 3:36am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics