select - SQL Server - 加入动态透视表中的选择列

问题描述

我尝试使用下面的代码动态旋转表 PMESATOVERVIEW 并将其连接到 PMEOBJECT 另一个表。但是我想从透视表中删除某些列。

首先我将描述数据示例：

PMEOBJECT
ObjectId, Location,
1, North
2, South
3, West
4, East

并内连接此表的旋转版本：

PMESATOVERVIEW
ObjectId, AttributeCat, AttributeId, Value
1, WW, Surface, 40
1, XX, Date, 1-1-2017
1, WW, Energy Label, B
2, WW, Surface, 45
2, XX, Date, 3-5-2017
3, WW, Surface, 50
3, WW, Energy Label, A
4, WW, Surface, 50
4, XX, Date, 1-4-2018
4, WW, Energy Label, A+

使用以下代码：

DECLARE @colsValues 

AS NVARCHAR(max) = Stuff(
(SELECT DISTINCT ',' + 
    Quotename(fat.ATTRIBUTEID)
     FROM   PMESATOVERVIEW fat
     FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @query AS NVARCHAR(max) =  
                                'SELECT    *
                                  FROM       (SELECT *
                                              FROM   PMESATOVERVIEW
                                              PIVOT(Max(VALUE)
                                              FOR ATTRIBUTEID IN ('+ 
@colsValues +')) piv) fat
                                  INNER JOIN PMEOBJECT tb
                                  ON          tb.OBJECTID = fat.OBJECTID';
EXECUTE(@query)

此代码可以正常工作，但我得到的结果是：

PMESATOVERVIEW
ObjectId    AttributeCat    Date    Energy Label    Surface ObjectId    Location
1   WW  NULL        C       40      1   North
2   WW  NULL        B       40      2   West
3   WW  NULL        A+      45      3   South
4   WW  NULL        NULL    50      4   East
1   XX  1-1-2017    NULL    NULL    1   North
2   XX  3-4-2017    NULL    NULL    2   West
4   XX  1-5-2018    NULL    NULL    4   East

我想要的是：

ObjectId    Date    Energy Label    Surface ObjectId    Location
1   1-1-2017    C       40  1   North
2   3-4-2017    B       40  2   West
3   NULL        A+      45  3   South
4   1-5-2018    NULL    50  4   East

SQL语句结果：http ://rextester.com/DKHF52968

任何想法如何做到这一点？我试图在 Pivot 语句中只选择几列，但不知何故它无法识别 ObjectId、AttributeId 和 Value。

对此有什么想法吗？

亲切的问候，伊戈尔

标签： selectdynamicpivot-tablemultiple-columns

通过执行类似的操作，您可以获得预期的输出。但是，可能有更好的方法来做到这一点。

DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(AttributeId) 
         FROM   PMESATOVERVIEW       
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @maxCols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ', max(' + Quotename(AttributeId) + ') as ' + Quotename(AttributeId)
         FROM   PMESATOVERVIEW       
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @query AS NVARCHAR(max) =  'SELECT     fat.ObjectId, '+@maxCols+',  max(tb.location) as Location 
                                    FROM       (SELECT ObjectId, '+ @cols +'
                                                FROM   PMESATOVERVIEW 
                                                PIVOT(Max(Value) FOR AttributeId IN ('+ @cols +')) piv) fat
                                    INNER JOIN PMEOBJECT tb 
                                    ON         tb.ObjectId = fat.ObjectId 
                                    group by   fat.ObjectId';

EXECUTE(@query)

输出

+----------+----------+--------------+---------+----------+
| ObjectId |   Date   | Energy Label | Surface | Location |
+----------+----------+--------------+---------+----------+
|        1 | 1-1-2017 | C            |      40 | North    |
|        2 | 3-4-2017 | B            |      40 | West     |
|        3 | NULL     | A+           |      45 | South    |
|        4 | 1-5-2018 | NULL         |      50 | East     |
+----------+----------+--------------+---------+----------+

在线演示：http ://rextester.com/GVY57614

select - SQL Server - 加入动态透视表中的选择列

问题描述

解决方案

推荐阅读