Merge 3 Rows Into 1 With SSIS

Apr 6, 2008

I have 3 input sources, date, name and text. What SSIS transformation could I use to merge these 3 rows into one single row. Example:

John Doe

Result wanted :

10/16/2007 John Doe Text.......................................

Thank you so much fro your help.


View 9 Replies


Merge Rows Into One?

Mar 25, 2008

Does anyone have any idea how to construct a select statement querying a temp table that contains the following data:

Code|Jan-Apr| May-Aug| Sep-Dec

... that will merge the results as follows?

Code|Jan-Apr| May-Aug| Sep-Dec
ABCD|250.00| 889.56| 1233.67

Your help would be greatly appreciated!


View 3 Replies View Related

Select Merge Rows

Jul 25, 2014

I am trying to build a SQL select query, but am struggling a little bit.I have a table with multiple inventory transactions, each with an ID and weight.

For example:

ID SysProg Weight
210359 271 986
210359 238 985
210359 61 984
210359 5 983
954679 271 1000
354852 271 568
354852 238 523
354852 59 521
354852 5 520

What I want to do is write a SQL query that returns records where there is a transaction for SysProg = 238, but return the weights for some of the other transactions in columns on the same row.Basically, from the above data set, what I would like to return is:

ID Weight (271) Weight (238) Weight (61 or 59)
210359 986 985 984
354852 568 523 521

How to start this?

View 2 Replies View Related

Merge Two Rows In The Same Table

Jul 23, 2005

I want to create a stored procedure that will merge columns from tworows that contain duplicated contacts.I have can easily identify the duplicates and extract the UniqueIDs asparameters, but I can't figure out how to construct the actual updateSQL.@KeeperID int,@DupeID intUpdate ContactsSETa.Info1 = LEFT(TRIM(IsNull(a.info1,'') + ' ' IsNull(b.Info1,''))255),a.Info2 = LEFT(TRIM(IsNull(a.info2,'') + ' ' IsNull(b.Info2,'')),255),etc, etc...FROM(here's what I can't figure out)Contacts a ID = @KeeperIDContacts b ID = @DupeID

View 2 Replies View Related

Merge A Column From Several Rows Into One Row

Dec 20, 2007

I have an old db I'm converting with SSIS.
In the old db I have an Log-table wich is in this format:

each CaseId can have several rows.

Now I want to merge the Note Row into one row based on the CaseID.
So that I get One Row per CaseID with one bignote.

I have been looking into Pivot, but just dont seem to get it working.
Any Ideas?

View 2 Replies View Related

How To Merge Data From Two Different Rows

Aug 29, 2006

Hi Guys,

Can anyone please explain and show an example of how to merge data from one row to another using in SQL 2000

thanks in advance

View 4 Replies View Related

Merge/Combine Rows

Aug 2, 2006

I am writing a database system which recieves information parsed from various data formats. These data formats may or may not be complete, and as such some rows in the database can have gaps.
The input formats may contain reference to the same row (in this case the same Company record) but hold different facts about that company.
Eg one message may have name, phone and fax, whereas another message may contain name, address and website.
I need to be able to insert new companies into the database OR update current records with extended data as relevant, ive been looking for UPSERT or MERGE queries in SQL Server but i cant find any useful resources explaining its use.
Alternativly i'd like to be able to condense potential duplicates into single rows, eg:
which would combine into one row where the null values get set by values derived from other similar rows:
Name PhoneEmailWebsite

Any help at all would be really appreciated

View 6 Replies View Related

Transact SQL :: How To Merge Two Rows Into One Row

Sep 24, 2015

I have a table that has columns like following


Is there an easy way to turn this into format below ??



View 5 Replies View Related

All Rows Are Not Replicated Correctly (merge)

Sep 28, 2007

Hi all! Hope you can help us with this one. We installed SP 2 of Sql server 2005 few months ago, and that caused a strange problem with merge replication.

We have about 150 subscribers in one merge publisher, and 200.000 new rows generated each day (retail system). Most tables are filtered.

Replication and filtering works fine, except 0,1% --> 0,2% of rows are not replicated correctly. These "problem-rows" are always replicated correctly to the publisher database, but publisher can't always replicate these rows further to other subscribers. Before SP2 everything worked fine.

Same problem occurs with filtered and non-filtered tables.

SQL Server shows no errors or warnings, everything SEEMS to work fine.

We have to make "dummy"-updates (like update SALES set id=id) in publisher database, to wake up the replicator. And always after these dummy updates all problem-rows will be replicated correctly from the publisher to subscribers.

Why do we have to "wake up" the replicator time to time with dummy updates? Is there a bug in SP2?

View 3 Replies View Related

Merge Join - No Output Rows

Apr 24, 2008


I have a problem with a Merge Join providing no output (when it should have 1890 rows). My Data Flow Task has 4 OLE Data Sources, 3 Multicasts, and 1 OLE Data Destination. I am experiencing the problem near the end of my data flow where two Multicasts create two parallel flows of data (see Level 1 below). I have two Merge Joins which join one leg from each multicast with a leg from the other multicast (see Level 2 below). Then the two remaining legs use a Merge to get my destination output (see Level 3 below).

I am experiencing my problem with the Merge Join (input A2, B2) --> (output C2) transformation. The Merge Join providing output C1 appropriately outputs 1890 rows, but C2 outputs 0 rows. Both Merge Joins are identical. The data is identically sorted prior to entering the problematic Merge Join and a DataViewer (Grid) verified that the data is appropriately entering in. Merge Join (input A2, B2) --> (output C2) has 667 rows as input A2 and 1890 rows as input B2 (using an inner join, just like the other merge join), but C2 baffles me with 0 rows of output (when it too should have 1890). I receive no Ouput errors and the execution completes showing all green.

Level 1
Multicast (output A1, A2) [667 rows]
Multicast (ouput B1, B2) [1890 rows]

Level 2
Merge Join (input A1, B1) --> (output C1) [1890 rows]
Merge Join (input A2, B2) --> (output C2) [0 rows]

Level 3
Merge (input C1, C2) --> (output D1) [1890 rows]*

I read about mysterious behavior with Merge Joins and have attempted modifying my EngineThreads property to values between 2 and 10, with no luck. Any help/ideas would be appreciated.



* Should be 3780 rows

View 4 Replies View Related

How To Merge Multiple Rows Into Single Row

May 13, 2008


I have two tables of news feed NewsHeader & NewsDetails
Time Header
10:15:34 AM News1
10:15:34 AM News1
10:15:34 AM News1
11:19:39 AM News2
11:19:39 AM News2
12:35:04 PM News3
12:35:04 PM News3

Time Text RowC
10:15:34 AM ABC 1
10:15:34 AM DEFG 2
10:15:34 AM HIJKL 3
11:19:39 AM AABB 1
11:19:39 AM CCDD 2
12:35:04 PM ZZYY 1
12:35:04 PM XXWW 2

Required Output
Time Header Text
10:15:34 AM News1 ABCDEFGHIJKL
11:19:39 AM News2 AABBCCDD
12:35:04 PM News3 ZZYYXXWW

Thank you.

View 2 Replies View Related

Merge Two Rows Only If TagWidgetIDs Same But Tag Values Different?

May 15, 2015

I have this stored procedure which generates the following output.

The query which generates the output from the stored procedure is as follows:

SELECT tat.[agent-id],
tat.[agent-name] AS [AgentName],
core.fnInitialCapitalization(t.DisplayName) AS TagName,

[Code] ....

How can I join the output rows only if the TagWidegetIds are same but the TagValues, TagIndex and ValueIndex columns are different?

View 3 Replies View Related

Ssis Package Design To Load Only Rows Which Are Changed From Exisiting Rows.

Aug 17, 2007

Hi i tried designing a SSIS package which loads only those rows which were different from existing rows in the table , i need to timestamp the existing row with an inactive date when a update of that row is inserted (ex: same studentID )
and the newly inserted row with a insert time stamp
so as to indicate the new row as currently active, in short i need to maintain history and current rows in same table , i tried using slowly changing dimension but could not figure out, anyone experience or knowledge regarding the Data loads please respond.

example of Data would be like

exisiting data

12 DDS 14 M XYZ ST 2/4/06 NULL
14 hgS 17 M ABC ST 3/4/07 NULL

New row to insert would be

12 DDS 15 M DFG ST 4/5/07

the data should reflect

12 DDS 14 M XYZ ST 2/4/06 4/5/07

12 DDS 15 M DFG ST 4/5/07 NULL

14 hgS 17 M ABC ST 3/4/07 NULL

Please provide your input as much as you can even though it might not be a 100% solution.

View 4 Replies View Related

Using SSIS 2005 To Strip Out Bad Rows In Excel And Then Insert Detailed Rows Into OLE DB Data Source

Apr 6, 2006

Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
Problem & Info:
I am bringing in an Excel file.  I need to first strip out any non-detail rows such as the breaks you see with totals and what not.  I should in the end have only detail rows left before I start moving them into my SQL Table.  I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here:

Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table.  I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
Desired Help:

How to perform
1)       stripping out all undesired rows
2)       importing each column into sql table

View 1 Replies View Related

SQL Server 2012 :: Merge Multiple Rows Into One?

Nov 26, 2014

basically i have data like this

order_key comment
1 A
1 B
1 C
2 B
2 D

the data intends to be like this

order_key comment
1 A,B,C
2 B,D

View 3 Replies View Related

SQL Server 2012 :: How To Merge Multiple Rows Into One Row

Dec 24, 2014

I have a table that looks like this ...


I want to merge the data to look like this ...

idphone1 phone2

Basically if the type code is 1 one then move the data to column phone1, if the type is 2 then move it to column phone2.

This would be fairly simple if we always have type codes 1 and 2. But sometimes we can have type 1 and not type 2, or we could have type 2 and not type1.

Right now we only have 2 type codes. But, in the future we could be adding a 3rd type. So that would add a 3rd column (phone3).

Below is my code that I have written. I move the data into a temp table then list it. I am thinking of making this a view to my table. It works just fine. My question is, is there a better and more efficient way of doing this?

CREATE TABLE #Contacts (
phone1 VARCHAR(15),
phone2 VARCHAR(15)

-- Insert the records for type 1

FROM test1
WHERE type_code = '1'

-- Insert the records for type 2, if the id does not exist for type 1

FROM test1
FROM #Contacts
AND test1.type_code = '2'

-- if the id has both type 1 and 2, update the phone2 column with the data from type 2

UPDATE #Contacts
SET phone2 = test1.phone_num
FROM #contacts
JOIN test1 ON =
WHERE type_code = '2'
SELECT id, phone1, phone2
FROM #Contacts
DROP TABLE #Contacts

View 2 Replies View Related

T-SQL (SS2K8) :: Merge Multiple Rows In Single Row

Jul 8, 2015

I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.

CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical

I'm using T-SQL to generate the column type. Below is my DDL.

USE tempdb
(Prefix char(8),
Code char(5)

[Code] ....

View 7 Replies View Related

Merge Replication Mysteriously Deleting Rows

Aug 23, 2006

I am running a simple merge replication in SQL Server 2000. I have one database that is the publisher, and a second database that is the subscriber. When I add a new row to the subscriber it will replicate to the publisher as expected. However, the new row at the subscriber will then be deleted without explanation. The row will remain at the publisher though.

Does anyone know why it is doing this?

View 8 Replies View Related

Merge Subscribers Pull Twice The Amount Of Rows

May 26, 2007

We are running merge replication, SQL Server 2005 Enterprise with SQL Mobile 2005 (Windows Mobile 5) subscribers. Partitions are filtered on HOST_ID.

Ocassionally we experience a situation where a subscriber experiences an unusually long synchronization duration, and upon examining Replication Monitor, it appears that twice the number of rows, or X the number of rows (up to 7 times the number of rows) that should have been inserted are recorded as synchronized for the session: once the normal amount as inserts and once the normal amount as updates. This occurs for all tables in the subscription. This occurs on a first time synchronization to an empty subscriber database where there should be only inserts taking place.

I have examined the snapshot partition folders for these users on the file system and they appear to be identical in size and content as other subscribers. Checking the last partition snapshot job run and other characteristics for the the subscriber in question, everything appears to be the same as other subscribers functioning normally.

The HOST_ID for us is an employee ID used to filter employee specific data. I have seen this happen if the subscriber changes the value for the HOST_ID used in filtering, after the mobile database has already been populated (2 employees attempt to use the same mobile device and database). But, we have seen this happen recently where the HOST_ID was apparantly never changed midstream.

This just started happening recently. The only modification around the same time frame was the implementation of a custom business logic handler/custom conflict resolver that performs like "Latest Wins" but has logic added to update the a last-update datetime column for selected transaction tables at time of synchronization, so that an SSIS job can detect the changed rows for copying incremental database changes to another application database. This all seems to be working perfectly.

Any ideas?



View 1 Replies View Related

DB Engine :: Merge Multiple Rows Into Single Row

Jul 8, 2015

I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.

CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical

I'm using T-SQL to generate the column type. Below is my DDL.

USE tempdb
(Prefix char(8),
Code char(5)


View 2 Replies View Related

Transact SQL :: Merge 2 Rows Into One Line Output

May 21, 2015

How to summarise the data in this table to a single row output per site (2 records for every SiteID). I am based in the UK so the data copied from SQL is in the US format. I can convert this to UK date without any issues.

TABLE [dbo].[MRMReadings](
[SiteIncomeID] [int] IDENTITY(1,1)
[SiteID] [nchar](5)

[Code] ....     

Is it possible to return the data in the following format merging 2 lines of data into one output:

 SiteID   ReadStartDate  ReadEndDate      ReadStartIncome     ReadEndIncome
L0020     19/05/2015 05:00  20/05/2015 05:00    85.98     145.98
L0101     19/05/2015 22:07    20/05/2015 22:14         1,936.08       1,438.89
L0102     20/05/2015 21:16   19/05/2015 21:48   143.65  243.5

I am using SQL 2008 R2.

View 12 Replies View Related

Transact SQL :: Merge Multiple Rows Onto A Single Row

May 13, 2015

I have a set of data which contains individual logon and logoff data. The table is as follows:

1234,       2015-05-12,    15 (For Logon) or 25 (For Logoff)

I'm hoping to extract this data as follows:


View 19 Replies View Related

SQL Server 2008 :: Merge Multiple Rows Of Same ID Into Single Row

Feb 19, 2015

I need the requirements of merging multiple rows of same ID as single row.

My Table Data:


Required Output Should be,


How to achieve this output. Tried grouping but its not working also producing the same result.

View 1 Replies View Related

SQL Server 2008 :: How To Merge Two Rows Into One (conditions Or Pivot)

Jul 8, 2015

Merge two rows into one (conditions or Pivot?)

I have Temptable showing as

Columns:EmpName, Code, Balance

Rows1: EmpA, X, 12
Rows2: EmpA, Y, 10

I want to insert the above temp table to another table with column names defined below like this

Empname, Vacation Hours, Sicks Hours
EmpA, 12, 10

Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.

View 3 Replies View Related

SQL Server 2012 :: Merge Column Values Into Rows?

Aug 12, 2015

I need to merge column values (#Status.Status) based on OrderID onto #Orders.NewStausCombined field separated by commas .

OrderID INT,
Status VARCHAR(20)
INSERT INTO #Status ( OrderID, Status )


View 3 Replies View Related

Transact SQL :: Merge Two Rows Into One Based On Same Customer Number

Oct 26, 2015

I have a table Customer with below column

CustomerNumber , FName,LName,DOB

I have either 2 or 1 row for each customer number  I want to write a SQL to pull data into 1 table based on same CustomerNumber, if only one row is present for the particular customer number it should pull null in FName2,LName2,DOB2 columns.

CustNumber  Fanme   Lname   DOB
1  Sam tiller 08/26/1981
1  Joe   timy  01/02/1986
2  jack  niks   09/09/1990

Result I want:
CustNumber Fname1  Lname1 DOB1 Fname2 Lname2 DOB2
1  Sam  Tiller 08/26/1981 Joe  timy 01/02/1986
2  Jack  niks  09/09/1990 null null null

View 7 Replies View Related

Merge Allmost Identical Rows For Output Table

Dec 20, 2007

I need to transform Foxpro table to SQL Server table with merging all rows into one where all column values are the same except one . For this the only column with the different values , I want them also to be merged as coma or space delimited string. The question whether SSIS is a good candidate for this kind of data munging and also would be interested to know knowing as many as possible ways of doing that. Surely I may produce Foxpro script in 5 minutes which wil do that and be a pre-processor action before SSIS starts.

View 3 Replies View Related

Merge Replication: Missing Rows At Subscriber With No Conflicts

Jul 18, 2007

Hi all,

We are using a mix of SQL 2005 and 2000 servers and our "main" database server is running SQL 2005 x64 (SP2 ver. 3042).

Our system has run perfectly for months, then subsequent to an SP2 update we are seeing several instances where the data record counts are different for several tables among all the servers.

We are using Merge Replication, with no filters and published every 2 minutes.

Any ideas?



View 1 Replies View Related

Only 9999 Rows After MERGE JOIN In SQL Server BIDS

Feb 5, 2007

I've gote 2 Tables with about 50.000 rows and I left outer join them with MERGE JOIN.

The result are 9999 rows. Has anybody got the same problem. Maybe it's a bug!?

View 2 Replies View Related

Merge Multiple Rows Into A One Or More Rows With Multiple Columns

May 7, 2008

Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:


1 A

2 A
3 A
4 A
5 A
6 B

7 B
8 B
9 C

10 D

11 D

I want output for the above as:

Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null

Please help me. Thanks!

View 6 Replies View Related

SQL Server 2012 :: Merge Multiple Rows From Single Table

Nov 17, 2014

I have resulting rows from a query similar to the following:

The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:

[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,

[Code] ....

I basically want to merge the like Employee ID's together into a single row like the following:

I know I have done this before and it is probably pretty simple.

View 4 Replies View Related

Merge Replication Downloading Rows Immediately After Uploading SQL 2005

Jan 9, 2007


I have noticed that four out of the 145 tables being replicated in my topology perform a mysterious update.

If I insert data at the subscriber then synchronise the upload is completed followed by immediate downloading of the same identical set of rows as updates.

Has anyone seen anything like this. I cannot see any difference in the filter joins or article settings compared to any other table in the system but these four consistently do this.

Publisher/Distributor: SQL 2005 SP1 on Win 2003 Server
Subscriber: SQL Express 2005 SP1 on Win XP SP2


View 5 Replies View Related

Merge Join: Nr Of Output Rows Unchanged When Amount Of Input Changes

May 25, 2007

Dear all,

I created a package that seems to work fine with a small amount of data. When I run the package however with more data (as in production) the merge join output is limites to 9963 rows, no matter if I change the number of input rows.

Situation as follows.

The package has 2 OLE DB Sources, in which SQL-statements have been defined in order to retrieve the data.

The flow of source 1 is: retrieving source data -> trimming (non-key) columns -> sorting on the key-columns.

The flow of source 2 is: retrieving source data -> deriving 2 new columns -> aggregating the data to the level of source 1 -> sorting on the key columns.

Then both flows are merged and other steps are performed.

If I test with just a couple of rows it works fine. But when I change the where-clause in the data source retrieval, so that the number of rows is for instance 15000 or 150000 the number of rows after the merge join is 9963.

When I run the package in debug-mode the step is colored green, nevertheless an error is displayed:

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Merge Join" (4703) failed with error code 0xC0047020. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

To be honest, a few more errormessages appear, but they don't seem related to this issue. The package stops running after some 6000 rows have been written to the destination.

Any help will be greatly appreciated.

Kind regards,


View 4 Replies View Related

Copyrights 2005-15, All rights reserved