文章/答案/技术大牛

发布

社区首页 >问答首页 >压缩3数据库表并将行转换为列- SQL Server

问压缩3数据库表并将行转换为列- SQL Server
EN

Stack Overflow用户

提问于 2016-02-02 23:51:58

回答 1查看 44关注 0票数 0

我有这三个数据库表

**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       | 
    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

那是3数据库表。tblWeb.WebID = tblWebServices.WebID

我需要获取AppName of tblWeb，并将tblSites站点转换为列，并且在站点下面应该是tblWebServices中的“最新”应用程序名称摘要状态，所有没有数据的站点都应该标为“无数据找到”。

预期的输出如下：

**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

这是我想要的输出。我已经在这里搜索了所有的例子和问题，但似乎没有任何意义。有人能帮我在Server中配置一些有关此输出的代码吗？

这是密码。请帮我配置这个。请!

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

这些代码的输出如下

|  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

我不需要WebID在场。我需要显示在预期输出中的AppName。

请帮助我，你的意见和回答将是一个很大的帮助。谢谢

sql-server

回答 1

Stack Overflow用户

回答已采纳

发布于 2016-02-03 02:34:11

我在summary_state表中添加了tblWebServices列并修改了脚本，它应该可以工作：

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'') 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 
LEFT JOIN tblWeb ON t.WebID = tblWeb.WebID 
WHERE t.rn = 1 GROUP BY tblWeb.AppName ORDER BY tblWeb.AppName '
PRINT @sql
EXEC sp_executesql @sql

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/35166061

复制

相似问题

问压缩3数据库表并将行转换为列- SQL Server
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问压缩3数据库表并将行转换为列- SQL ServerEN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问压缩3数据库表并将行转换为列- SQL Server
EN