Answer 1

Hello,

Please go through this link , you will find it easy by writing TSQL

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Do everything in TSQL and then just copy that query in OLEDB Source and you will be done. 

In SSIS you can use PIVOT Transformation to achieve this but that can be little hard to configure sometime.

Thanks

Answer 2

The difficulty comes in the need to do a "look up forward" because you have variable number of rows. So the best approach is to probably use a temp/staging table. Besides, the process you referred to is often called TRANSPOSING ROWS.

I would do that using a stored  procedure using a PIVOT. Please see this example  to get you started: http://www.logiclabz.com/sql-server/transpose-rows-to-columns-in-sql-server-2005-using-pivot.aspx

Answer 3

Here is TSQL for your Requirement

CREATETABLE #T
 (
   CUSTOMERNO INT,
   NoteNo   INT,
   NoteText  VARCHAR(50),
   Seq    INT
 )

GO

INSERTINTO #T
VALUES   (1234,
      1,
      'Example of a multi',
      1),
      (1234,
       1,
       'line note.',
       2),
      (5555,
       2,
       'Single line note.',
       3),
      (6666,
       3,
       'Example of a multi',
       4),
      (6666,
       3,
       'line note  that spans',
       5),
      (6666,
       3,
       'three rows.',
       6) 
SELECT * FROM #T
          
           
SELECTDISTINCT CUSTOMERNO
        ,NoteNo
        ,Note_Text
FROM  #T P1
    CROSS APPLY(SELECTStuff((SELECT',' + NoteText
                 FROM  #T P2
                 WHERE P2.CUSTOMERNO = P1.CUSTOMERNO
                 FORXML PATH('')), 1, 1, '')) D (Note_Text) 


--1234	1	Example of a multi,line note.--5555	2	Single line note.--6666	3	Example of a multi,line note that spans,three rows.

Answer 4

Hi again,

I've tried a couple of these methods with no success. A lot of this stuff is kind of going over my head, but I'm trying to copy the logic so I can see an output  and maybe wrap my mind around it. I'll just refer to the actual field  names I'm working with here. I realized for this table  I don't have a NoteID so instead I'm using the KHDATE field. Basically I need the transformed data to be KHCUST, KHDATE and the concatenated KHTEXT field. 

I tried the Pivot method, but I'm obviously not understanding it because it isn't outputting anything:

SELECT KHCUST, KHDATE, [Text]
FROM
(SELECT KHCUST, KHDATE, KHTEXT
FROM BILLING.CMP00800) AS TableToBePivoted
PIVOT
(
KHTEXT
FOR KHCUST AND KHDATE IN ([Text])
) AS PivotedTable;

Then I tried the XML PATH method, but it doesn't seem to be doing much. I can still see many ungrouped rows  in the results. It's giving me 212611 results where a SELECT * on the table gives me 212633, so SOMETHING is happening, just not much. For example, the first two results in the list have the same KHCUST and KHDATE, so they should be a single  row.

SELECTDISTINCT KHCUNQ, KHDATE, KHTEXT
FROM BILLING.CMP00800 p1
CROSS APPLY ( SELECT KHTEXT + ' 'FROM BILLING.CMP00800 p2
           WHERE p2.KHDATE = p1.KHDATE AND p2.KHCUNQ = p2.KHCUNQ
           ORDERBY KHCUNQ, KHDATE
           FORXML PATH('') ) D ( Notes )

Also, neither of these methods seem to reference my sequencing number (KHKEY), so I don't know how it would know what order to concatenate  in.  I'll keep trying, but if anyone can see where I'm going wrong here, let me know!

Answer 5

Hello,

Try this 

SELECT DISTINCT KHCUNQ, KHDATE, Notes

FROM BILLING.CMP00800 p1

CROSS APPLY ( SELECT KHTEXT + ' '

           FROM BILLING.CMP00800 p2

           WHERE p2.KHDATE = p1.KHDATE 

           FOR XML PATH('') ) D ( Notes )

You will get less records as you are concatenating the duplicate records for same KHDATE.

Thanks

Answer 6

Thank you Aamir, that helped me understand it a bit better. I modified it slightly, but this query is now doing exactly what I want:

SELECTDISTINCT KHCUNQ, KHDATE, Notes
FROM BILLING.CMP00800 p1
CROSS APPLY ( SELECT KHTEXT + ' 'FROM BILLING.CMP00800 p2
      WHERE p2.KHDATE = p1.KHDATE AND p2.KHCUNQ = p1.KHCUNQ
		  ORDERBY KHKEY ASCFORXML PATH('') ) D ( Notes )
ORDERBY KHCUNQ

Still not entirely sure I understand how it works, but at least it works! Thanks everyone.

Answer 7

You are welcome DanWallace,

Here are few links that you can go through to understand

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Cross apply

http://weblogs.sqlteam.com/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx

Thanks