need help on SQL - SQL Server Forums

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 on SQL

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-11-08 : 06:21:56

I've tables and data as following,

declare @tH table
(idx int, employer int, statementNo varchar(20))
insert into @tH values(2, 1, 'S0803/2011/11/1');
insert into @tH values(3, 3, 'S0900/2011/11/1');
/*
idx is a primary key
statementNo is unique
*/


declare @tD table
(idx int identity, tH_idx int, chequeNo varchar(20), amt decimal(10,2))
insert into @tD(tH_idx, chequeNo, amt) values(2, '19254474', 1000.00);
insert into @tD(tH_idx, chequeNo, amt) values(2, '19254475', 180.00);
insert into @tD(tH_idx, chequeNo, amt) values(3, '88488998', 2500);
/*
idx is a primary key with identity
relationship between @tH and @tD is 1 to many
tH_idx is foreign key to @tH(idx)
*/

if @tH(idx)=2, how to display output as following,

idx   | employer    | statementNo         | chequeNo                     | totalAmt
----------------------------------------------------------------------------------------------
2       1             S0803/2011/11/1       19254474,19254475              1180.00

if @tH(idx)=3, how to display output as following,

idx   | employer    | statementNo         | chequeNo                     | totalAmt
----------------------------------------------------------------------------------------------
3       3             S0900/2011/11/1       88488998                       2500.00

really need help

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-08 : 06:32:19

select th.idx, th.statement_no, SUM(td.amt)
from @th th
join @td td
on th.idx = td.th_idx
group by th.idx, th.statement_no

then you need something to concatenate the cheque numbers. A cte would do it or a function.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-11-08 : 06:35:14

how to concatenate the cheque numbers sir?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 06:54:54

[code]
select t.idx, t.employer,t.statementNo,
stuff((select ','+ chequeNo from @tD where tH_idx = t.idx order by chequeNo for xml path('')),1,1,'') as chequeNo,
t1.TotalAmt
from @tH t
cross apply (select sum(Amt) as TotalAmt
from @tD
where tH_idx = t.idx
)t1
where t.idx = @yourpassedvalue
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-11-08 : 06:56:44

your answer is my inspiration

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 07:47:03