Home » SQL ServerRSS

Transforming a multi-row notes table into a single-row notes table

Hi guys,

I'm new to SSIS and trying to design a package that will take a multi-row notes table and concatenate it into a single-row notes table. For example, the source fields may include Customer#, Note#, NoteText, Sequence#. In the current format, a long note may be stored in 2, 3, 4 rows. My SSIS package somehow needs to loop through each row, ordered by Sequence#, and concatenate the NoteText field into a single output field. For example:

 

Customer# Note# NoteText      Sequence#
--------- ----- --------      ---------
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 spans5
6666   3   three rows.     6

 

Needs to output the following:

 

Customer# Note# NoteText 
--------- ----- --------   
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. 

 

Does anyone know what the best method is to accomplish this? I tried using a Script Task and writing it in VB.NET but I wasn't able to figure out how to access multiple rows of data at once. I don't have much experience writing T-SQL Stored Procedures, but I could learn quickly if this is the best method.

Any suggestions?

 

7 Answers Found

 

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.
Thanks

 

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.

 
 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter