Combining 3 tables in SQL Server

I have this 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
    1      bWeb     www.bWeb.com    ServerA
    1      cWeb     www.cWeb.com    ServerB
    1      dWeb     www.dWeb.com    ServerA
    1      eWeb     www.eWeb.com    ServerC
    1      fWeb     www.fWeb.com    ServerC
    1      gWeb     www.gWeb.com    ServerD
    1      hWeb     www.hWeb.com    ServerD

tblWebServices
| Sites | WebID | SummaryState |       Last_Check       | 
    A       1         OK         02/01/2016 10:00:00.000
    A       1       Critical     02/01/2016 10:00:04.000
    A       2         OK         02/01/2016 10:00:04.000
    A       2       Critical     02/01/2016 10:00:06.000
    A       3         OK         02/01/2016 10:00:07.000
    A       3         OK         02/01/2016 10:00:09.000
    A       4         OK         02/01/2016 10:00:10.000
    A       4         OK         02/01/2016 10:00:12.000
    A       5       Critical     02/01/2016 10:00:14.000
    A       5         OK         02/01/2016 10:00:17.000
    A       6         OK         02/01/2016 10:00:20.000
    A       6         OK         02/01/2016 10:00:23.000
    A       7         OK         02/01/2016 10:00:25.000
    A       7       Critical     02/01/2016 10:00:36.000
    A       8         OK         02/01/2016 10:00:39.000
    A       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 the 3 Database table. the tblWeb.WebID = tblWebServices.WebID

And i need to get the AppName of tblWeb and convert the Sites of tblSites into Column and also under the Sites should be the "LATEST" Summary State of Appname in tblWebServices and all sites that has no data should be labeled "No Data Found"

The expected output is like 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 is my desired output. I already search all examples and questions here but nothing seems to make sense to me. Can somebody help me configure some codes in SQL Server regarding this output.

This is the codes that I configure with some answers in this problem

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'') AS '  + QUOTENAME('Site-'+Sites) + CHAR(10)
FROM tblSites
ORDER BY Sites
SELECT @sql = @sql + 'FROM ( SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY Sites , WebID ORDER BY Last_Check DESC) FROM tblWebServices ) t WHERE rn = 1 GROUP BY WebID ORDER BY WebID '
PRINT @sql
EXEC sp_executesql @sql

The output of this codes is like 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 the WebID to be present. I need the AppName. Please help me with this Your comment and answer will be a big help. Thank you

Answers


It seems like the only difference between what you want and what you get is that you want the application name but you get the application id, so simply select the application name instead of the application id.

Select application_name, 
MAX(CASE WHEN site-code = 'MK' Then MaxRoundTrip else NULL end) as [summaryMK], 
MAX(CASE WHEN site-code = 'MK' and timestamp=MaxRoundTrip then [summary-state]  else NULL end) as [timestampMK], 
MAX(CASE WHEN site-code = 'MV' Then MaxRoundTrip else NULL end) as [summaryMV], 
MAX(CASE WHEN site-code = 'MV' and timestamp=MaxRoundTrip then [summary-state] else NULL end) as [timestampMV]
from (select *, MAX(timestamp) Over(Partition by web_apps_id, site-code) as maxroundtrip from tblServices) tblServices
group by application_name

you can join your tables ant get application name.

Select 
Web.application_name
MAX(CASE WHEN site-code = 'MK' Then MaxRoundTrip else NULL end) as [summaryMK], 
MAX(CASE WHEN site-code = 'MK' and timestamp=MaxRoundTrip then [summary-state]  else NULL end) as [timestampMK], 
MAX(CASE WHEN site-code = 'MV' Then MaxRoundTrip else NULL end) as [summaryMV], 
MAX(CASE WHEN site-code = 'MV' and timestamp=MaxRoundTrip then [summary-state] else NULL end) as [timestampMV]
  from 
     (select 
         *, 
         MAX(timestamp) Over(Partition by web_apps_id, site-code)
as maxroundtrip 
   from tblServices) tblServices Service
left join tblWeb Web on Service.web_apps_id= Web.web_apps_id
group by web_apps_id

Need Your Help

Sticking a UIView to the bottom of a UICollectionView that works in 3.5 and 4 inches

objective-c ios7 uicollectionview autolayout

I am trying to stick a UIView, containing an ADBannerView, to the bottom of my UICollectionView that works in both 3.5 and 4". My current implementation works for 4" but it gets cut off in 3.5".

New-Object : The term New-Object is not recognized as the name of a cmdlet

powershell sharepoint-2013 windows-server-2012

I want to configure high trusted app for app dev in SharePoint, and to do so, i need first to insert some commands in the powershell editor like :