sql - Concatenate Rows using FOR XML PATH() from multiple tables -



sql - Concatenate Rows using FOR XML PATH() from multiple tables -

apologies if there reply in here, cannot find it...can concatenate rows multiple tables using xml path. allow me explain....

i have next 4 tables:

"businesssupport" has reference other 3 tables

"application", "businessprocess" , "orgaunit" (contains organizational info such service line , geography)

select a.name [app name], stuff((select ',' + bp.name [text()] businessprocess bp left bring together businesssupport bs on bp.refstr=bs.xobject bs.object=a.refstr xml path('')), 1, 1, '' ) [business process] , stuff((select ',' + org.name [text()] orgaunit org left bring together businesssupport bs on org.refstr=bs.yobject bs.object=a.refstr xml path('')), 1, 1, '' ) [service lines] application

this gives me table results such

appname; business process; service lines

app1; process1, process2, process 3; sl1, sl2, sl3

app2; process1, process 2; sl1, sl4, sl4

app3; process4, process 2; sl3, sl5, sl6

however, have new dimension have been asked study from. table 5 "relations" has reference business back upwards , orgaunit.

i have like

appname; business process; service lines; geography

app1; process1, process2, process 3; sl1, sl2, sl3; geography1, geography2

app2; process1, process 2; sl1, sl4, sl4; geography1, geography3

app3; process4, process 2; sl3, sl5, sl6; geography3, geography4, geography5

i have tried next not results returned:

stuff((select ',' + org2.name [text()] orgaunit org2 left bring together relations rel on rel.toref=org2.name left bring together businesssupport bs on bs.refstr=rel.fromref bs.object=a.refstr xml path('')), 1, 1, '' ) [geog]

thanks

if of columns returned when using for xml concatenate values homecoming null, entire concatenated string become null, since "some string" + null null sql server. if possible org2.name null in example, wrap isnull around concatenated results, , replace empty string.

stuff((select isnull(',' + org2.name, '') [text()] orgaunit org2 left bring together relations rel on rel.toref=org2.name left bring together businesssupport bs on bs.refstr=rel.fromref bs.object=a.refstr xml path('')), 1, 1, '' ) [geog]

sql concatenation

Comments

Post a Comment

Popular posts from this blog

Delphi change the assembly code of a running process -

Delphi change the assembly code of a running process - i'm trying alter address 00741fa5 has push test.009e721c . alter push test.009e71c8 . procedure callback; asm force $9e71c8 end; procedure tform2.btn1click(sender: tobject); var ppid :dword; pprocess : cardinal; begin getwindowthreadprocessid(findwindow(nil,pchar('test')), @ppid); pprocess := openprocess(process_all_access, false, ppid); injectasm(pprocess, $741fa5, 10, integer(@callback), 0); end; here injectasm function (with error checking) function injectasm(process: longword; injectaddress, injectsize, codeaddress, codesize : integer): pointer; var csize, rsize : integer; replaced : array of byte; jmp : array [0..4] of byte; jmpaddress : integer; nopv : byte; : integer; nbr: ulong_ptr; begin //injectsize must equal or greater 5, because need space our //(far) jmp wwxxyyzz instruction //if there's no space, inject in place of few instructions if injectsize &
Read more

java - Parsing XML, skip certain tags -

java - Parsing XML, skip certain tags - i next mkyong's great tutorial on how parse xml in java, have situation need read tags within area. e.g. <?xml version="1.0"?> <company> <staff id="1001"> <firstname>yong</firstname> <lastname>mook kim</lastname> <nickname>mkyong</nickname> <salary>100000</salary> </staff> <staff id="2001"> <firstname>low</firstname> <lastname>yin fong</lastname> <nickname>fong fong</nickname> <salary>200000</salary> </staff> <changes> <staff id="2001"> <firstname>low</firstname> <lastname>yin fong</lastname> <nickname>fong fong</nickname> <old_salary>175000</salary> <ne
Read more

c# - ASP.NET MVC Sequence contains no matching element -

c# - ASP.NET MVC Sequence contains no matching element - i new asp.net mvc. have finished project unusual issue appeared in project. as seek run project error message. an exception of type 'system.invalidoperationexception' occurred in entityframework.dll not handled in user code additional information: sequence contains no matching element in 1 of controller using statement below groups display in dropdownlist. http://localhost....../clients (trying run page) , getting error on line containing code below. viewbag.groupdropdownvalues = new selectlist(db.groups.orderby(m => m.groupid), "groupid", "groupname"); or other controller: viewbag.categorydropdownvalues = new selectlist(db.category.orderby(m => m.categoryid), "categoryid", "categoryname"); this happening in controllers using syntax. i need display dropdownlists values database. working correctly till morning. don't have thought changed ,
Read more