Improve company productivity with a Business Account.Sign Up

x
?
Solved

How can I dynamically build view columns from table values

Posted on 2014-02-11
2
Medium Priority
?
199 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 1500 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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

607 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