Create View from dynamic fields

Question

Create View from dynamic fields

Dinculescu Mihai

Old Hand

Points: 327
More actions
December 28, 2010 at 10:33 am

#229101

Hello
I'm working on a .NET application with dynamic fields so instead of building a classic table named Assembly with id, colA, colB I had to build a Fields table having colA & colB as records. Then I've built a AssemblyData table which holds values for colA & colB.
So I have this 3 tables:
Assembly: AssemblyId
Fields: FieldId, FieldName
AssemblyData: AssemblyId, FieldId, Value
Now I'd like to create a view named AssemblyView which will return the assembly like it would be a normal table: AssemblyId, ColA, ColB.
Any ideas if this is possible ?
Thanks
Mihai

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You might be able to get what you want with Pivot/Unpivot.

It looks like you're heading down the "one true lookup table" path. Keep in mind that those always appear to be a solution to just about everything with regard to object-relational programming, but they end up being a horrific nightmare to maintain and actually use once they get more than a few rows of test data in them.

I can't be sure from your post, but it has that look to it. Read up on the subject and make sure you're not getting into that, just to be on the safe side.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

AppleMouth SSC-Addicted Points: 450 More actions · Answer 2

Have you looked at Computed Columns (Persisted and probably Deterministic)?

A nod is as good as a wink to a blind bat.

Dinculescu Mihai Old Hand Points: 327 More actions · Answer 3

I've found the solution using a dynamic PIVOT

declare @sql nvarchar(max), @Cols nvarchar(max)

;with cte as (select distinct F.FieldName from AssemblyData A

inner join Fields F on A.FieldID = F.FieldID)

select @Cols = stuff((select ', ' + quotename(FieldName) from cte ORDER by FieldName for XML PATH('')),1,2,'')

select @sql = 'select * from (select A.AssemblyID, A.Value, F.FieldName

from AssemblyData A inner join Fields F on A.FieldID = F.FieldID) src

PIVOT (max(Value) for FieldName IN (' + @Cols + ')) pvt'

execute (@SQL)

GSquared SSC Guru Points: 260824 More actions · Answer 4

Of course, you can't use dynamic SQL in a view, but that'll work for a proc.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Dinculescu Mihai Old Hand Points: 327 More actions · Answer 5

Indeed, looks like it's not possible to do it inside a view.