时间戳

首页 > 解决方案 > 需要帮助从具有动态属性的表创建透视视图

tsql - 需要帮助从具有动态属性的表创建透视视图

问题描述

我有一个大约 80k 行的表格，格式如下：

+--------+-----------+-------------------+---------------------+----------------------+-----------------+----------------+-----------------+
| SiteID | SubSiteID | DynamicPropertyID | DynamicPropertyName | DynamicPropertyValue | StaticProperty1 | StaticPropety2 | StaticProperty3 |
+--------+-----------+-------------------+---------------------+----------------------+-----------------+----------------+-----------------+
|      1 |         1 |                 1 | Property1           | ABC                  |               1 |              1 |               1 |
|      1 |         1 |                 2 | Property2           | XYZ                  |               1 |              1 |               1 |
|      1 |         1 |                 3 | Property3           | DEF                  |               1 |              1 |               1 |
|      1 |         2 |                 1 | Property1           | GHT                  |               1 |              1 |               1 |
|      1 |         2 |                 2 | Property2           | XYZ                  |               1 |              1 |               1 |
|      1 |         2 |                 3 | Property3           | WWF                  |               1 |              1 |               1 |
|      2 |         1 |                 1 | Property1           | FHS                  |               1 |              1 |               1 |
|      2 |         1 |                 2 | Property2           | HHS                  |               1 |              1 |               1 |
|      2 |         1 |                 3 | Property3           | BSF                  |               1 |              1 |               1 |
|      2 |         2 |                 1 | Property1           | QDD                  |               1 |              1 |               1 |
|      2 |         2 |                 2 | Property2           | FFF                  |               1 |              1 |               1 |
|      2 |         2 |                 3 | Property3           | YTR                  |               1 |              1 |               1 |
+--------+-----------+-------------------+---------------------+----------------------+-----------------+----------------+-----------------+

我需要做的是为该表创建一个视图，将表转换为以下格式：

+--------+-----------+-----------+-----------+-----------+-----------------+-----------------+-----------------+
| SiteID | SubSiteID | Property1 | Property2 | Property3 | StaticProperty1 | StaticProperty2 | StaticProperty3 |
+--------+-----------+-----------+-----------+-----------+-----------------+-----------------+-----------------+

我面临的问题是，我们可能会随着时间的推移向表中添加新的“动态属性”，因此如果我们添加到每个 SiteID/SubsiteID 组合，视图需要能够动态更改以添加新列Property4，例如.

非常感谢任何建议或帮助。

标签： tsqlsql-server-2012ssms

解决方案

试试下面的查询：

DECLARE @samplex TABLE
(
SiteID INT,
SubSiteID  INT,
DynamicPropertyID  INT,
DynamicPropertyName  VARCHAR(10),
DynamicPropertyValue VARCHAR(10)

);
INSERT @samplex
(
    SiteID,
    SubSiteID,
    DynamicPropertyID,
    DynamicPropertyName,
    DynamicPropertyValue

)
VALUES
(1, 1, 1, 'Property1', 'ABC'),
(1, 1, 1, 'Property2', 'ABC'),
(1, 1, 1, 'Property3', 'ABC'),
(1, 1, 1, 'Property4', 'ABC'),
(2, 1, 2, 'Property5', 'ABC'),
(2, 1, 2, 'Property6', 'ABC'),
(2, 1, 2, 'Property1', 'ABC'),
(2, 1, 2, 'Property2', 'ABC'),
(3, 1, 2, 'Property3', 'ABC'),
(3, 1, 2, 'Property4', 'ABC'),
(3, 1, 3, 'Property5', 'ABC'),
(4, 1, 4, 'Property6', 'ABC');

SELECT *
FROM @samplex
    PIVOT
    (
        MAX(DynamicPropertyValue)
        FOR [DynamicPropertyName] IN ([Property1], [Property2], [Property3], [Property4], [Property5], [Property6])
    ) pvt;

推荐阅读