SSIS 将多行合并成单行 [英] SSIS Combine multiple rows into single row
问题描述
我有一个包含 6 列的平面文件:NoteID、Sequence、FileNumber、EntryDte、NoteType 和 NoteText.NoteText 列有 200 个字符,如果注释长于 200 个字符,则文件中的第二行包含该注释的延续.它看起来像这样:
I have a flat file that has 6 columns: NoteID, Sequence, FileNumber, EntryDte, NoteType, and NoteText. The NoteText column has 200 characters and if a note is longer than 200 characters then a second row in the file contains the continuation of the note. It looks something like this:
|NoteID | Sequence | NoteText |
---------------------------------------------
|1234 | 1 | start of note text... |
|1234 | 2 | continue of note.... |
|1234 | 3 | more continuation of first note... |
|1235 | 1 | start of new note.... |
我如何在 SSIS 中将多行 NoteText 合并为一行,以便该行如下所示:
How can I in SSIS combine the multiple rows of NoteText into one row so the row would like this:
| NoteID | Sequence | NoteText |
---------------------------------------------------
|1234 | 1 | start of note text... continue of note... more continuation of first note... |
|1235 | 1 | start of new note.... |
非常感谢任何帮助?
Dim NoteID As String = "-1"
Dim NoteString As String = ""
Dim IsFirstRow As Boolean = True
Dim NoteBlob As Byte()
Dim enc As New System.Text.ASCIIEncoding()
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.NoteID.ToString() = NoteID Then
NoteString += Row.NoteHTML
IsFirstRow = True
Else
If IsFirstRow Then
Output0Buffer.AddRow()
IsFirstRow = False
End If
NoteID = Row.NoteID.ToString()
NoteString = Row.NoteHTML.ToString()
End If
NoteBlob = enc.GetBytes(NoteString)
Output0Buffer.SingleNoteHTML.AddBlobData(NoteBlob)
Output0Buffer.ClaimID = Row.ClaimID
Output0Buffer.UserID = Row.UserID
Output0Buffer.NoteTypeLookupID = Row.NoteTypeLookupID
Output0Buffer.DateCreatedUTC = Row.DateCreated
Output0Buffer.ActivityDateUTC = Row.ActivityDate
Output0Buffer.IsPublic = Row.IsPublic
End Sub
我现在的问题是我必须将输出列从 Wstr(4000) 转换为 NText,因为有些注释太长了.当它导入我的 SQL 表时,它只是乱码字符,而不是实际的注释.
推荐答案
在 SQL Server Management Studio(使用 SQL)中,您可以使用 stuff
函数与 XML Path 轻松组合 NoteText 字段
将您的行值组合到一个列中,如下所示:
In SQL Server Management Studio (using SQL), you could easily combine your NoteText field using stuff
function with XML Path
to combine your row values to a single column like this:
select distinct
noteid,
min(sequence) over (partition by n.noteid order by n.sequence) as sequence,
stuff((select ' ' + NoteText
from notes n1
where n.noteid = n1.noteid
for xml path ('')
),1,1,'') as NoteText
from notes n;
您可能想研究一下在 SSIS 中做类似事情的东西.查看有关如何在 SSIS 中创建脚本组件以执行类似操作的链接:SSIS 脚本组件 - 连接行
You will probably want to look into something along the line that does similar thing in SSIS. Check out this link on how to create a script component in SSIS to do something similar: SSIS Script Component - concat rows
这篇关于SSIS 将多行合并成单行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!