如何在一行SQL服务器中获得并保持多行值 [英] How to get sum and hold multiple rows value in one row SQL server

问题描述

我有一个sql表。此表代表的价值如

I have one sql table. And this table represent value like

<br />
bank | branch  | chqno | chqdated    | Amount | childName | class | RecieiptNo<br />
ban1 | branch1 | 11111 | 12.12.2017  | 200    | child1    | 1-A   | 1  <br />
ban1 | branch1 | 11111 | 12.12.2017  | 300    | child2    | 1-B   | 2<br />
ban1 | branch1 | 11111 | 12.12.2017  | 300    | child3    | 1-C   | 3<br />
<br />
ban2 | branch2 | 22332 | 12.12.2017  | 400    | child3    | 2-B   | 4<br />

现在我的问题是如何显示我的数据，例如

Now my question is how can I show my data like

bank | branch  | chqno | chqdated    | Amount | child_class                            | RecieiptNo
ban1 | branch1 | 11111 | 12.12.2017  | 800    | child1 (1-A), child2 (1-B),child3 (1-C)| 1,2,3
ban2 | branch2 | 22332 | 12.12.2017  | 400    | child3 (2-B)                           | 4

为此我运行查询和查询如下，请看某人。





谢谢



我的尝试：

For this I have run query and query is below please see someone.


Thanks

What I have tried:

select distinct bank,bracnh, chqno,chqdated,Amount ,
STUFF((select ', '+ childName +' ('+ class +')' 
from dbo.temp t1 where t1.chqno=t2.chqno and t1.bank=t2.bank and  t1.bracnh=t2.bracnh and 
t1.chqdated=t2.chqdated and t1.Amount=t2.Amount for XML path ('')),1,1,'') as [child name] from dbo.temp t2 

推荐答案

我认为，目前这个查询应该有效。

I think, for now, this query should work.

SELECT  bank, branch, chqno, SUM(Amount) Amount
       ,STUFF((SELECT ', ' + CAST(ReceiptNo AS VARCHAR(10)) [text()]
         FROM dbo.code1 
         WHERE bank = t.bank
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') ReceiptNo

		,STUFF((SELECT ', ' + childName + ' (' + Class + ')' [text()]
         FROM dbo.code1 
         WHERE bank = t.bank
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') child_class
FROM dbo.code1 t
GROUP BY bank, branch, chqno

输出：

Output:

bank	branch	chqno	Amount	ReceiptNo	child_class
ban1	branch1 11111	  800	1, 2, 3	     child1 (1-A), child2 (1-b), child3 (1-c)
ban2	branch2	22332	  400	 4	         child3 (2-B)

需要考虑的事项：未来的表演问题，以及处理大量数据，可能会重写调用函数然后交叉连接。

Something to think about: performances issue, in the future, when dealing with large amount of data, maybe rewrite to call a function then cross join.

这篇关于如何在一行SQL服务器中获得并保持多行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！