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