sql server - Converting NULLs to 0 in SQL Pivot -


the following code generates pivot (shown below code)

select *  ( select glaccount , dc , amount  accountingtxns acctno = '178523' ) tabledata pivot ( sum (amount) dc in ([d],[c]) )  sumamountdc 
 glaccount             d       c interestdue     3801.37 3731.68 cash            25600   25000 intreceivable   3801.37 3801.37 intincome       null    3684.47 intoverdue      null    116.9 principal      25000    21868.32

how rid of nulls (i.e. convert them 0's)?

use coalesce,

select  glaccount,          coalesce(d, 0) d,          coalesce(c, 0) c          (             select  glaccount,                      dc ,                      amount                  accountingtxns                acctno = '178523'         ) tabledata         pivot         (             sum(amount)             dc in ([d],[c])         ) sumamountdc

Comments

Post a Comment

Popular posts from this blog

monitor web browser programmatically in Android? -

i've got tricky question here. need users make payment bank (namely barclaycard) in uk. so, have https url , add parameters (such amount pay, order reference, etc) url, start http connection intent.actionview, redirect user browser can enter credit card details on bank's webpage , make payment our account successfully. far ? the code use below (i changed values privacy reasons) problem is, need app when user has completed/failed/cancelled payment. barclaycardautomatically redirects particular url when payment has succeeded, 1 if failed. there no way of knowing when barclaycard payment has succeeded go android app somehow ? button cardbutton = (button) findviewbyid(r.id.card_button); cardbutton.setonclicklistener(new view.onclicklistener() { @override public void onclick(view arg0) { string prehashstring = new string(); string prohashstring = new string(); string shapassphrase = new string(); shapassphrase = "gsvth£h70zkh...
Read more

Shrink a YouTube video to responsive width -

i have youtube video embedded on our website , when shrink screen tablet or phone sizes stops shrinking @ around 560px in width. standard youtube videos or there can add code make go smaller? you can make youtube videos responsive css. wrap iframe in div class of "videowrapper" , apply following styles: .videowrapper { float: none; clear: both; width: 100%; position: relative; padding-bottom: 56.25%; padding-top: 25px; height: 0; } .videowrapper iframe { position: absolute; top: 0; left: 0; width: 100%; height: 100%; } the .videowrapper div should inside responsive element. padding on .videowrapper necessary keep video collapsing. may have tweak numbers depending upon layout.
Read more

c# - Using multiple datasets in RDLC -

i working on rdlc reports, , reports work fine. got stuck when added 1 more dataset rdlc file. on adding dataset, added data source well. ran project, , report no more working giving error: a data source instance has not been supplied data source can 1 please guide me on steps take in order use multiple datasets. using visual studio 2012. multiple datasources added follows: reportviewer1.localreport.datasources.add(rds); reportviewer1.localreport.datasources.add(rds1); refer link: http://www.c-sharpcorner.com/uploadfile/robo60/standalonerdlcreports11142007183516pm/standalonerdlcreports.aspx all parts covered in this. also refer usefull discussion: http://forums.asp.net/t/1241964.aspx
Read more