Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

How can I dynamically build view columns from table values

I have 3 tables .
One is a table of objects (generically)

tbl_objects
object_pk   int
object          varchar

another table is a list of potential properties that may be applied to the object

tbl_properties
property_pk   int
property         varchar

and the third is a join of the objects to the property with a value

tbl_join
join_pk  bigint
object_pk    int
property_pk  int
value     varchar

I can easily create a view  that returns these records in line

Join_pk            Object_pk          object         property_pk         property         value
1                    1                     object1                      1       property1      somevalue
2                    1                     object1                    2       property2      anothervalue
3                     2                     object2                    1       property1      yetanothervalue



And so on

Thats easy enough..      but what I want to do is create a view that will dynamically post properties as a column depending on how many properites are in the properties table so that the table instead would read

object_pk            object           property1                property2         property3
1                    object1      somevalue             anothervalue      null
2                    object2      yesanothervalue      null                  null


Does anyone know if/how I can do this?
0
Bastyon
Asked:
Bastyon
1 Solution
 
Brian CroweCommented:
Completely dynamically? Not without some complicated script generation.  If you are willing to set a reasonable limit on the number of properties then you could try something like:

WITH cteProperty
AS
(
      SELECT join_pk, object_pk, property_pk, [value],
            ROW_NUMBER() OVER(PARTITION BY object_pk ORDER BY join_pk) AS PropertyIndex
      FROM tbl_join
)
SELECT o.object_pk, o.OBJECT,
      p1.[value] AS property1,
      p2.[value] AS property2,
      p3.[value] AS property3
FROM tbl_objects AS o
LEFT OUTER JOIN cteProperty AS p1
      ON o.object_pk = p1.object_pk
      AND p1.PropertyIndex = 1
LEFT OUTER JOIN cteProperty AS p2
      ON o.object_pk = p3.object_pk
      AND p2.PropertyIndex = 2
LEFT OUTER JOIN cteProperty AS p3
      ON o.object_pk = p3.object_pk
      AND p3.PropertyIndex = 3
-- Repeat as needed
0
 
Surendra NathCommented:
This requirement can be  properly fulfilled by using a dynamic pivot.
Note: the below sql is not tested, incase of any syntactical issues, please send the same to me
DECLARE @SQL NVARCHAR(4000)
DECLARE @SQ NVARCHAR(4)
SET @SQ = '''' 
;WITH C AS
(
SELECT DISTINCT property FROM <your View>
), CValue AS
(
SELECT STUFF(( SELECT ',' + '[' + property + ']' FROM C FOR XML PATH('') ),1,1,'')    
)
SET @SQL = ' select object_pk,object, ' + (SELECT * FROM CValue) 
+ ' FROM ( SELECT ' 
+ ' object_pk,object,property,value from <your View> ) PVT'
+ ' PIVOT ' 
+ ' ( MAX(VALUE) FOR property IN (' + (SELECT * FROM CVALUE)
+ ') AS P '

--PRINT @SQL
EXEC (@SQL)

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now