Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
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
Updated 1-Mar-17 16:08pm

1 solution

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.
 
Share this answer
 
Comments
Karthik_Mahalingam 1-Mar-17 22:16pm    
5
Bryian Tan 2-Mar-17 14:35pm    
Thank you!!!!
Garth J Lancaster 1-Mar-17 22:30pm    
nice !
Member 12245539 1-Mar-17 22:45pm    
5
Bryian Tan 2-Mar-17 14:35pm    
Thanks!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900