Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
FaithRaven
Starting Member
2 Posts |
 Posted - 2010-12-28 : 12:26:20
|
| HelloI'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: AssemblyIdFields: FieldId, FieldNameAssemblyData: AssemblyId, FieldId, ValueNow 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 ?ThanksMihai |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-12-28 : 13:49:07
|
| with what I understand from your post,I doubt its possibility in views but you can do it using stored procedure and then return the record from stored procedure.Please provide some sample data to understand your requirement. |
 |
|
|
FaithRaven
Starting Member
2 Posts |
Posted - 2010-12-28 : 15:23:36
|
| I've found the way do it inside a SP using PIVOT.Here's the code if anyone else wants it: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) srcPIVOT (max(Value) for FieldName IN (' + @Cols + ')) pvt'execute (@SQL) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
View from dynamic fields