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

SQL Fiddle 演示

这篇关于SSIS 将多行合并成单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！