在SQL中从SubQuery插入数据时,如何合并临时表中的列?

2020-02-14 sql sql-server tsql sql-server-2012

我有一个临时表#TestData,如下所示,其中使用子查询插入了数据

Id      Amount         Ccy    Description
2012    2373350.000    INR    test1
2012    2321030.000    INR    test12
2014    2350.000       INR    test4
2014    30.000         USD    test5
2014    130.000        USD    test6

代码已更新:使用填充和分组依据来串联字符串,但会得到错误的无效对象Tmpta

      INSERT  INTO #TestData

       -- SELECT  Tmpta.Id,Tmpta.Amount,Tmpta.Ccy,tmpta.Description
     SELECT  Tmpta.Id ,
                    STUFF(( SELECT  ',' + CAST(B.[Amount] AS VARCHAR(20))
                            FROM    Tmpta B
                            WHERE   B.id = Tmpta.id
                            ORDER BY B.[Amount]
                          FOR
                            XML PATH('')
                          ), 1, 1, '') AS Amount ,
                    STUFF(( SELECT  ',' + B.[cur]
                            FROM    Tmpta B
                            WHERE   B.id = Tmpta.id
                            ORDER BY B.[cur]
                          FOR
                            XML PATH('')
                          ), 1, 1, '') AS Ccy ,
                    STUFF(( SELECT  ',' + B.[Description]
                            FROM    Tmpta B
                            WHERE   B.id = Tmpta.id
                            ORDER BY B.[Description]
                          FOR
                            XML PATH('')
                          ), 1, 1, '') AS Description
         FROM    ( SELECT SubQuery
                  ) Tmpta              

         WHERE   tmpta.Id in (2012,2014)
         group by Tmpta.id

我想合并数据如下:

Id      Amount                         Ccy          Description
2012    2373350.000,2321030.000        INR,INR      test1,test12
2014    2350.000,30.000,130.000        INR,USD,USD  test4,test5,test6

知道如何实现吗?

请帮忙。 谢谢!

Answers

尝试下面的查询

    SELECT
    ID,  string_agg(Amount,',') As Amount, string_agg(ccy,',') as CCY, string_agg(description, ',')  
FROM
    <table name>
GROUP BY
    ID;

如果要使用子查询,则可以将该数据放入临时表中,并在查询中使用该表,但不使用子查询

删除表#Tmpta

选择ID,金额,金额,说明 INTO #Tmpta 从数据A

选择#Tmpta.Id, STUFF((SELECT','+ CAST(B. [Amount] AS VARCHAR(20)) 来自#Tmpta B 在哪里B.id =#Tmpta.id 按B订购。[金额] 对于 XML路径('') ),1,1,'')AS金额, STUFF((SELECT','+B。[当前] 来自#Tmpta B 在哪里B.id =#Tmpta.id B的订单。[当前] 对于 XML路径('') ),1,1,'')AS Ccy, STUFF((SELECT','+B。[说明] 来自#Tmpta B 在哪里B.id =#Tmpta.id B订购。[说明] 对于 XML路径('') ),1,1,'')AS描述 来自#Tmpta

     WHERE   #Tmpta.Id in (2012,2014)
     group by #Tmpta.id  

Related