How to get sum and hold multiple rows value in one row SQL server

Question

5.00/5 (1 vote)

See more:

SQL-server-2000

SQL-Server-2005

, +

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

Posted 1-Mar-17 15:45pm

Member 12245539

Updated 1-Mar-17 16:08pm

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bryian Tan · Accepted Answer · 2017-03-01T16:08:00

I think, for now, this query should work.

SQL

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.

How to get sum and hold multiple rows value in one row SQL server

1 solution

Solution 1

Add your solution here

Preview 0

How to get sum and hold multiple rows value in one row SQL server

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us