Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 need help to built SQL statement

Author  Topic 

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-11-01 : 01:57:10
I've table and data as following,

declare @t1 table
(
idx int,
statementNo varchar(20),
chequeNo varchar(20),
zakatAmount decimal(10,2)
)

/*
- idx is primary key
- chequeNo is unique
- combination of statementNo, chequeNo is unique
*/

insert into @t1 values(1,'N0011/2012/03/1','mbb14597',1500.00);
insert into @t1 values(2,'N0011/2012/03/2','mbb9999',1400.00);
insert into @t1 values(3,'N0011/2012/03/2','bbgbgb',100.00);
insert into @t1 values(4,'N0011/2012/04/1','gtgtgt',1400.00);
insert into @t1 values(5,'N0011/2012/04/1','gtgtgtx',100.00);
insert into @t1 values(6,'S0001/2012/04/1','cimb 8878',400.00);
insert into @t1 values(7,'S0001/2012/04/1','pbb89',200.00);
insert into @t1 values(8,'S1163/2012/05/1','cimb 8890',2570.45);
insert into @t1 values(9,'S1163/2012/05/2','PBB 9900',2570.45);


How is my SQL looks like to having output as following,

statementNo | chequeNo | zakatAmount
---------------------------------------------------------------
N0011/2012/03/1 mbb14597 1500.00
N0011/2012/03/2 mbb9999, bbgbgb 1500.00
N0011/2012/04/1 gtgtgt, gtgtgtx 1500.00
S0001/2012/04/1 cimb 8878, pbb89 600.00
S1163/2012/05/1 cimb 8890 2570.45
S1163/2012/05/2 PBB 9900 2570.45


Really need help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-01 : 03:59:33
[code]SELECT s1.statementNo,
STUFF((SELECT ', ' + s2.chequeNo FROM @t1 AS s2 WHERE s2.statementNo = s1.statementNo FOR XML PATH('')), 1, 2, '') AS chequeNo,
SUM(zakatAmount) as zakatAmount
FROM @t1 AS s1
GROUP BY statementNo
ORDER BY s1.statementNo
[/code]


Too old to Rock'n'Roll too young to die.
Go to Top of Page

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-11-01 : 04:41:08
tq sir
Go to Top of Page
   

- Advertisement -