将多行合并为一个“备忘录"nvarchar(最大) [英] Combine multiple rows into one "memo" nvarchar(max)

问题描述

2 表 1 与客户其他与 accno 信息

2 tables 1 with clients other with accno information

table 1:
file, accno <- nvarchar(50)
t1, 123a
t1, 456a
t1, 789a
t2, 012b
t3, 345c
t3, 678c

我想把表1转移到表2

table 2:
file, accno <- nvarchar(max)
t1, 123a
    456a
    789a
t2, 012b
t3, 345c
    678c

我可以在delphi中很容易地做到这一点，但它会花费将近6个小时，因为我在表1上过滤了接近300000条记录

i can do this is delphi very easy, but itll take forever almost 6 hrs because im filtering close to 300000 records on table 1

是否有一个 sql 查询可以让这个过程变得非常快，即使它需要 5 分钟

is there an sql query that can make this go super fast, even if it takes 5min

推荐答案

SELECT
    DISTINCT [File]
    ,STUFF(
        (SELECT ',' + accno
        FROM
            TableName it
        WHERE it.[File] = ot.[File]
        FOR XML PATH(''))

        ,1,1,'') AS [Memo]
FROM
    TableName ot

您可以调整此设置，但这会创建一个逗号分隔的帐户列表，我认为这正是您要查找的内容?您可以修改以使用备注字段中的行结尾而不是逗号.

You can adapt this but this will create a comma delimited list of your accno which I think is kind of what you are looking for? You can modify to do it with line endings in your Memo Field rather than commas.

这篇关于将多行合并为一个“备忘录"nvarchar(最大)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！