Need help creating a pivoted view from table with dynamic properties

Question

I have a table with ~80k rows in the following format:

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

What I need to do, is create a view for this table that pivots the table into the following format:

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

The issue I am facing, is that we may be adding new 'dynamic properties' over time to the table, so the view needs to be able to dynamically change to add a new column if we add Property4 to each SiteID/SubsiteID combination, for example.

Any advice or help with this is greatly appreciated.

any way that you could use a stored proc rather than a view. Unfortunately to create a truly dynamic pivot you would need to utilize dynamic sql. if that is an option then you can look at examples here https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — SFrejofsky, Aug 20 '18 at 15:20

score 0 · Answer 1 · edited Aug 20 '18 at 13:35

0

Try the query below:

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;

edited Aug 20 '18 at 13:35

Arulkumar

12,966
14
47
68

answered Aug 20 '18 at 10:11

Puneeth

33
1
7

Hi @Puneeth thanks for your response. Unfortunately, local variables and temporary tables are not allowed in `CREATE VIEW` statements. – Cam Aug 20 '18 at 10:24
@Cam Yes..! but try executing my query and implement the same logic in the View.. it'll work .. i tried the same logic 3 days back as per my client requirement same as yours .. i created the view using Pivot Query.. it worked – Puneeth Aug 20 '18 at 10:41
@Cam i think you'll require two views 1st view will result as per the table you've put in the Question and in 2nd view you can implement the pivot referring to my suggested answer .. and the result will look as per the 2nd table you've asked in the question – Puneeth Aug 20 '18 at 10:47

Need help creating a pivoted view from table with dynamic properties

1 Answers1