SQL Server - Group by with multiple joins and strings -


i have searched many similiar topics still struggling working. trying group on 1 column in particular after doing few table joins. wouldbe appreciated

output today

account system  system  plan code   account plan code   request 12345   sys1    sys1    p1         12345    p1          00001 12345   sys1    sys1    p2         null     null        null 12345   sys2    sys2    p3         12345    p3          00002 34567   sys1    sys1    p1         null     null        null 34567   sys1    sys1    p2         34567    p2          00003 45678   sys3    sys3    p4         null     null        null

desired output

account system  system  plan code   account plan code   request 12345   sys1    sys1    p1          12345   p1, p2      00001 12345   sys2    sys2    p3          12345   p3          00002 34567   sys1    sys1    p2          34567   p1,p2       00003 45678   sys3    sys3    p4          null    null        null

i joining few tables , want combine plan codes 1 row per each unique account system mapping.

  • tab1 = account , system
  • tab2 = system , plan code (mapping shows plan codes related each system)
  • tab3 = account,plan code, request #

i lost group tried 1 column group , error not group expression. included columns , not change output @ all.

select      a.account, a.system, b.system, b.pcode, c.account, c.pcode, c.request        mydb.tab1 left join   mydb.tab2 b  on      a.system = b.system left join   mydb.tab3 c on      a.account = c.account , b.pcode = c.pcode       a.account not null order    a.account

if not need concatenate plans, please see if following group by implementation suit needs. removed several columns script. comply aggregate query limitations used max around c.pcode (which min well) , max c.request seems choice rid of nulls there.

select      a.account, a.system, max(c.pcode), max(c.request)        mydb.tab1 left join   mydb.tab2 b  on      a.system = b.system left join   mydb.tab3 c on      a.account = c.account , b.pcode = c.pcode       a.account not null group a.account, a.system order    a.account

Comments

Post a Comment

Popular posts from this blog

ios - iPhone/iPad different view orientations in different views , and apple approval process -

i force app's home screen portrait , other views landscape. apple rejects app if this? if not , how force first screen launch in portrait mode? , force other screens launch in landscape. this not duplicate question search every possible answer in website. i force app's home screen portrait , other views landscape. apple rejects app if this? no apple not reject app this. have lot of apps doesn't support orientation. forcing on 1 screen won't cause app reject. if not , how force first screen launch in portrait mode? , force other screens launch in landscape. can on write : -(nsuinteger)supportedinterfaceorientations; function.
Read more

java Extracting Zip file -

i'm looking way extract zip file. far have tried java.util.zip , org.apache.commons.compress, both gave corrupted output. basically, input zip file contain 1 single .doc file. java.util.zip: output corrupted. org.apache.commons.compress: output blank file, 2 mb size. so far commercial software winrar work perfectly. there java library make use of this? this method using java.util library: public void extractzipnative(file filezip) { zipinputstream zis; stringbuilder sb; try { zis = new zipinputstream(new fileinputstream(filezip)); zipentry ze = zis.getnextentry(); byte[] buffer = new byte[(int) ze.getsize()]; fileoutputstream fos = new fileoutputstream(this.tempfolderpath+ze.getname()); int len; while ((len=zis.read(buffer))>0) { fos.write(buffer); } fos.flush(); fos.close(); } catch (filenotfoundexception e) { // todo auto-generated catch block ...
Read more

C# WinForm - loading screen -

i ask how make loading screen (just picture or something) appears while program being loaded, , disappears when program has finished loading. in fancier versions, have seen process bar (%) displayed. how can have that, , how calculate % show on it? i know there form_load() event, not see form_loaded() event, or % property / attribute anywhere. all need create 1 form splash screen , show before main start showing landing page , close splash once landing page loaded. class splashform { //delegate cross thread call close private delegate void closedelegate(); //the type of form displayed splash screen. private static splashform splashform; static public void showsplashscreen() { // make sure launched once. if (splashform != null) return; thread thread = new thread(new threadstart(splashform.showform)); thread.isbackground = true; thread.setapartmentstate(apartmentstate.sta); thread....
Read more