Concatinate 3 Database Table and Converting Rows into Column - SQL Server -

-

i have 3 database table

**tblsites** | sites | sitename |    aa     aaaaaaaa    bb     baaaaaab    cc     caaaaaac    dd     daaaaaad  **tblweb** | webid | appname |    appurl    | servername |      1      aweb     www.aweb.com    servera     2      bweb     www.bweb.com    servera     3      cweb     www.cweb.com    serverb     4      dweb     www.dweb.com    servera     5      eweb     www.eweb.com    serverc     6      fweb     www.fweb.com    serverc     7      gweb     www.gweb.com    serverd     8      hweb     www.hweb.com    serverd  **tblwebservices** | sites | webid | summarystate |       last_check       |            1         ok         02/01/2016 10:00:00.000           1       critical     02/01/2016 10:00:04.000           2         ok         02/01/2016 10:00:04.000           2       critical     02/01/2016 10:00:06.000           3         ok         02/01/2016 10:00:07.000           3         ok         02/01/2016 10:00:09.000           4         ok         02/01/2016 10:00:10.000           4         ok         02/01/2016 10:00:12.000           5       critical     02/01/2016 10:00:14.000           5         ok         02/01/2016 10:00:17.000           6         ok         02/01/2016 10:00:20.000           6         ok         02/01/2016 10:00:23.000           7         ok         02/01/2016 10:00:25.000           7       critical     02/01/2016 10:00:36.000           8         ok         02/01/2016 10:00:39.000           8         ok         02/01/2016 10:00:40.000     b       1       critical     02/02/2016 10:00:00.000     b       1         ok         02/02/2016 10:00:04.000     b       2       critical     02/02/2016 10:00:04.000     b       2         ok         02/02/2016 10:00:06.000     b       3       critical     02/02/2016 10:00:07.000     b       3       critical     02/02/2016 10:00:09.000     b       4       critical     02/02/2016 10:00:10.000     b       4       critical     02/02/2016 10:00:12.000     b       5         ok         02/02/2016 10:00:14.000     b       5       critical     02/02/2016 10:00:17.000     b       6       critical     02/02/2016 10:00:20.000     b       6       critical     02/02/2016 10:00:23.000     b       7       critical     02/02/2016 10:00:25.000     b       7         ok         02/02/2016 10:00:36.000     b       8       critical     02/02/2016 10:00:39.000     b       8       critical     02/02/2016 10:00:40.000

that's 3 database table. tblweb.webid = tblwebservices.webid

and need appname of tblweb , convert sites of tblsites column , under sites should "latest" summary state of appname in tblwebservices , sites has no data should labeled "no data found"

the expected output this:

**expected output:**  | appname |   site-aa   |   site-bb   |     site-cc     |     site-dd     |    aweb       critical        ok         no data found     no data found    bweb       critical        ok         no data found     no data found    cweb          ok        critical      no data found     no data found    dweb          ok        critical      no data found     no data found    eweb          ok        critical      no data found     no data found    fweb          ok        critical      no data found     no data found    gweb       critical        ok         no data found     no data found    hweb          ok        critical      no data found     no data found

this desired output. search examples , questions here nothing seems make sense me. can me configure codes in sql server regarding output.

this codes. please me configure this. please!

declare @sql nvarchar(max) set @sql = '' select @sql = 'select webid ' + char(10) select @sql = @sql + '   , isnull(max(case when sites  = ''' + sites  + '''then summary_state end), ''no data found'') '  + quotename('site-'+sites) + char(10) tblsites order sites select @sql = @sql + 'from ( select *, rn = row_number() over(partition sites , webid order last_check desc) tblwebservices ) t rn = 1 group webid order webid ' print @sql exec sp_executesql @sql

the output of codes this

|  webid  |   site-aa   |   site-bb   |     site-cc     |     site-dd     |      1        critical        ok         no data found     no data found      2        critical        ok         no data found     no data found      3           ok        critical      no data found     no data found      4           ok        critical      no data found     no data found      5           ok        critical      no data found     no data found      6           ok        critical      no data found     no data found      7        critical        ok         no data found     no data found      8           ok        critical      no data found     no data found

i didn't need webid present. need appname shown in expected output.

please me comment , answer big help. thank you

i added summary_state column in tblwebservices table , modified script, should work:

declare @sql nvarchar(max) set @sql = '' select @sql = 'select tblweb.appname ' + char(10) select @sql = @sql + '   , isnull(max(case when sites  = ''' + sites  + '''then summary_state end), ''no data found'') '  + quotename('site-'+sites) + char(10) tblsites order sites select @sql = @sql + 'from ( select *, rn = row_number() over(partition sites , webid order last_check desc) tblwebservices ) t  left join tblweb on t.webid = tblweb.webid  t.rn = 1 group tblweb.appname order tblweb.appname ' print @sql exec sp_executesql @sql

Comments

Post a Comment

Popular posts from this blog

Delphi XE2 Indy10 udp client-server interchange using SendBuffer-ReceiveBuffer -

-
i use delphi xe2 , indy10 udp protocol. can't receive server echo on client side if use receivebuffer method. got "socket error # 10040" although send small echo message server client. console application illustrate problem below. in advance. program project1; {$apptype console} {$r *.res} uses system.sysutils, idglobal, idbasecomponent, idcomponent, idsockethandle, idudpclient, idudpserver, idudpbase, idstack; type tudp_serv = class(tidudpserver) procedure udpsvudpread(athread: tidudplistenerthread; adata: tidbytes; abinding: tidsockethandle); end; var udpserver: tudp_serv; udpcl: tidudpclient; bsnd, brcv: tbytes; s: string; k: integer; //============================================================================== procedure tudp_serv.udpsvudpread(athread: tidudplistenerthread; adata: tidbytes; abinding: tidsockethandle); begin writeln(' server read: ' + tohex(adata, length(adata))); abinding sendto(peerip, peerport, adat...
Read more

Qt ActiveX WMI QAxBase::dynamicCallHelper: ItemIndex(int): No such property in -

-
i new windows programming. here goes code objiwbemlocator = new qaxobject("wbemscripting.swbemlocator"); objwmiservice = objiwbemlocator->querysubobject("connectserver(qstring&,qstring&)",qstring("."),qstring("root\\cimv2")); qaxobject* returnlist = objwmiservice->querysubobject("execquery(qstring&)", qstring("select * %1").arg(domain)); qaxobject* result = returnlist->querysubobject("itemindex(int)", 0); i getting error on runtime qt activex wmi qaxbase::dynamiccallhelper: itemindex(int): no such property in but itemindex method exists msdn says the itemindex method not work collections not contain swbemobjects, such swbemmethodset, swbemnamedvalueset, swbemprivilegeset, swbempropertyset, , swbemqualifierset. what selecting ? have tried iterating on collection ? plus need minimum windows vista itemindex method. hope helps
Read more

Enable autocomplete or intellisense in Atom editor for PHP -

-
i'm first time user of atom editor php code. atom can autocomplete built-in functions of php. possible make editor autocomplete custom function written in separate file? function.php <?php function printsomething() { echo "hello world"; } index.php <?php require_once "function.php"; printsomething(); // autocompleted atom editor while typing thanks in advance. there text file in %userprofile%.atom called snippets.cson. in there can add snippet. more this: http://flight-manual.atom.io/using-atom/sections/snippets/ but need think can use later on other files well. otherwise need copy , paste.
Read more