Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How can I dynamically build view columns from table values

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

705 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