Solved

How can I dynamically build view columns from table values

Posted on 2014-02-11
2
189 Views
Last Modified: 2014-02-20
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
Comment
Question by:Bastyon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 39850430
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39850699
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question