Dynamic Data View

Hi All,

Was wondering what your feelings were on the best way to write a query to present the following data. I was going to write something dynamic which would just created a temp table and inserts the data in stages but thought i would post up here as well to see if there is a better way to do it.

I have a set of 3 tables as follows.

DataClass (List of data classes)


DataColumnMap (List of columns in each data class)

DataClassID REFERENCE to DataClass

DataStore (Holds the value data)
DataColumnMapID REFERENCE to DataColumnMap
InstanceID REFERENCE to InstaceList

The reason for doing this is that there are literally thousands of different data classes which all have a row per instance of use i want them to be stored all in one place. Logically speaking i fell the above design makes the most sense.

Answers are very much appreciated

LVL 16
Who is Participating?
LowfatspreadConnect With a Mentor Commented:
by multiple attrbutes (eg hobbies i meant joe bloggs  chess dominos ludo running expertsexchange....)

still not clear what you are wanting to do and why.. output wise

but if you will have many different dataclass mappings then i assume that dynamic sql will be required (generally bad)

i'd suggest that you dynamically format a pivot query along the lines of

select instanceid, [1] as firstname,[2] as lastname
from datastore as x
pivot (max([value]) for datacolumnmapid in ([1].[2])) as p
order by instanceid

you will need to construct the list of pivot columns and the select list rename via

declare @cols varchar(3000),@sel varchar(3000)
select @cols='',@sel=''
select @cols = @cols+',['+convert(varchar(10),id)+']'
  @sel=@sel+@sel+',['+convert(varchar(10),id)+'] as ['+columnname+']'
from datacolumnmap
where dataclassid = xxx
select @cols=stuff(@cols,1,1,'').@sel=@stuff(@sel,1,1,'')
can you tell us what you expected output would be?

what is the scope/purpose of the data?

is it time related?
are data changes significant?
   (e.g. do you need to track object data history...)

do you have any other tables to track relationships between the instance data?

how are you dealing with multiple attributes for the same instance?
  (e.g. a persons hobbies)

could you show/provide a data sample?
EvilPostItAuthor Commented:
can you tell us what you expected output would be?

imagine the data contained in the columnmap table is the horizontal column names of a table. The output should be presented like a select from a standard table

what is the scope/purpose of the data?

To store all of the config data in a single place as there are many variations i want to keep this dynamically stored so that table creation would not be needed from the front end.

is it time related?


are data changes significant?

Not at this point but maybe in the future, but i can manage this.

do you have any other tables to track relationships between the instance data?

the instanceid would be the identifier for a row of data effectively.

how are you dealing with multiple attributes for the same instance?

As above

could you show/provide a data sample?


1, TestClass


1, 1, FirstName
2, 1, LastName

1, 1, 0x1, Joe
2, 2, 0x1, Bloggs
3, 1, 0x2, Jane
4, 2, 0x2, Doe

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

EvilPostItAuthor Commented:
Thought i would re-answer one of your questions based upon the example i have given

can you tell us what you expected output would be?

[FirstName]     | [LastName]
Joe                  | Bloggs
Jane                | Doe
EvilPostItAuthor Commented:
Yep this is exactly what i was looking at doing, was just wondering if there would be a more efficient way of doing this using a cte or other clever trickery.
not that i can think of if you want the column headers....

if you had the "columnname:=" value  as the output horizontally  then a for xml clause would do it...
on a join between the map and datastore table.
EvilPostItAuthor Commented:
Sorry dont follow. Could you give an example?
like this

                ' '+m.columnname+':='+[s.value]
                datacolumnmap as m
                inner join datastore as s
                 on m.id=s.datacolumnmapid
                s.instanceid = x.instanceid
              and m.dataclass=?????
            FOR XML PATH ('')
        ), 1,1,'') as [data]
    datastore as x
EvilPostItAuthor Commented:
In actual fact my data was far more complicated due to the volume of rows etc. I wrote the following for the dynamic pivot which will suit any number of columns.

declare @var1 varchar(max)
declare @var2 varchar(max)
declare @sqlcmd varchar(max)
select @var1='',@var2=''
@var1=@var1+',max(['+convert(varchar(10),MapOrder)+']) as ['+isnull(MetaDataFriendlyColumnName,MetaDataRadiaColumnName)+']
from metadatacolumnmap
select @sqlcmd='select 
'+@var1+'from MetaDataStore as x
pivot (max(MetaDataValue) for MetaDataColumnMapID IN ('+left(@var2,(len(@var2)-1))+')) as p
group by RowNumber'
exec (@sqlcmd)

Open in new window

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.

All Courses

From novice to tech pro — start learning today.