• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

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)

ID
DataClassName

DataColumnMap (List of columns in each data class)

ID
DataClassID REFERENCE to DataClass
ColumnName

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

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

0
EvilPostIt
Asked:
EvilPostIt
  • 5
  • 4
1 Solution
 
LowfatspreadCommented:
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?
0
 
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?

No

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?

DataClass

1, TestClass

DataColumnName

1, 1, FirstName
2, 1, LastName

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

Thanks
0
 
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LowfatspreadCommented:
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,'')
0
 
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.
0
 
LowfatspreadCommented:
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.
0
 
EvilPostItAuthor Commented:
Sorry dont follow. Could you give an example?
0
 
LowfatspreadCommented:
like this

SELECT
    instanceid,
     stuff(
        (
            SELECT
                ' '+m.columnname+':='+[s.value]
            FROM
                datacolumnmap as m
                inner join datastore as s
                 on m.id=s.datacolumnmapid
            WHERE
                s.instanceid = x.instanceid
              and m.dataclass=?????
            FOR XML PATH ('')
        ), 1,1,'') as [data]
FROM
    datastore as x
ORDER BY
    instanceid
0
 
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=''
select 
@var1=@var1+',max(['+convert(varchar(10),MapOrder)+']) as ['+isnull(MetaDataFriendlyColumnName,MetaDataRadiaColumnName)+']
',
@var2=@var2+'['+convert(varchar(10),MapOrder)+'],'
from metadatacolumnmap
select @sqlcmd='select 
RowNumber
'+@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

0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now