Solved

How can I dynamically build view columns from table values

Posted on 2014-02-11
2
186 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
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now