Import duplicate row data into a derived column

user_15297269 0
Viewed 80

I have an excel table where we run samples in duplicate and I would like to import the results from the duplicate run into a derived column so that there would only be one row per sample in my destination table.

The data might look like this:

ID result mean
Aa 145 146
Aa 147 146

And I would like the destination to look like this

ID result1 result2 mean
Aa 145 147 146

Any ideas on how to solve this?

etl duplicates ssis excel sql
2 Answers

You can use an intermediate SQL Table like below :

  1. First Data flow to get data from Excel file to the intermediate table :

enter image description here

Second Data flow to PIVOT the duplicate rows :

enter image description here

This is your PIVOT query :

CREATE TABLE StagingTable ([ID] varchar(20), Result float, Mean float)
INSERT INTO StagingTable ([ID] , Result , Mean ) VALUES ('Aa',145,146)
,('Aa',147,146)

WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY [ID] ORDER BY [ID]) AS Rn FROM StagingTable
)
select 
    ID,
    MAX(case when RN = 1 then Result end) Result1,
    MAX(case when RN = 2 then Result end) Result2,
    Mean
from CTE
GROUP BY ID,Mean

ID | Result1 | Result2 | Mean
:- | ------: | ------: | ---:
Aa |     145 |     147 |  146

db<>fiddle here

user_95177690

I think I will just generate an import that redirects the duplicates to a second table and use a view to joint the tables and select the extra column from the duplicates table.

user_152972690
Related