SQL Create Dynamic Table View From Another Table

Problem:
I have a few related tables that collects information about a given publication. The information collected varies based on the type of collection and is stored in a few different tables. The latest requirement the customer wants is to alert the faculty when a publication is added that is 90% similar to another publication. To facilitate this I want to create a flat lookup table that I can easily query and manipulate.

The Database

Expand|Select|Wrap|Line Numbers

 
---------------

PUBLICATIONS

---------------

PublicationID (int)

PublicationTypeID (int)

AuthorList (varchar)

Synopsis (varchar)
 
------------------

PUBLICATION_TYPE

------------------

PublicationTypeID (int)

PublicationTypeName (varchar)
 
----------------------

PUBLICATION_TYPE_FIELD

----------------------

FieldID (int)

PublicationTypeID (int)

FieldType (varchar) 

FieldName (varchar)

Required (bit)
 
------------------------

PUBLICATION_INFORMATION

------------------------

PublicationID (int)

FieldID (int)

FieldValue (varchar)

The Question
Is it plausible or even reasonable to create a flat table view that would allow all the information be available in a single table?

IE:
==================================================
PublicationID | PublicationType | Field Name | Field Name | Field Name |
---------------------------------------------------------
1 | Book | Value | Value | Value |

Where the additional columns in the table are dynamically created by the available field type possibilities in another table?

Or have I lost my marbles and need to try a different approach?

Mar 3 '11 #1

Subscribe Post Reply

2507

ck9663

2,878

Expert 2GB

Yes it's plausible. Though I would limit it to the columns you need. Creating a a view on the fly could mean additional rights given to the one executing your query.

Good Luck!!!

~~ CK

Mar 5 '11 #2

by: Deja User | last post by:

This is probably very simple but I can't figure out a way to update one table from another table. Here is an example: ------ TABLEA ------ first_name last_name

Microsoft Access / VBA

Best way Copying DataRow from one table to another table?

by: Mike9900 | last post by:

What is the best way to copy DataRow from one table to another table, without copying its structure, which means copying only its data. -- Mike

C# / C Sharp

how to transfer a set of records from a table from another table

by: thengfen | last post by:

Hi! Im having a problem in transfering a set of records from a table to another table. The scenario is when i select combo box (Course taken such as diploma in IT), then the process will...

Microsoft Access / VBA

to add column from one table to another table

by: satishkhachane | last post by:

-------------------------------------------------------------------------------- I want to select one or more columns from one typed datatable and add it to another table . how I can do this ? ...

.NET Framework

Query to insert field from one table into another table - no unique identifier

by: lcourchesne | last post by:

Hi there, This is what I am trying to do: Create a query that will insert a column from Table 2 into Table 1. These two tables are exactly the same in structure, however, there is no unique...

Microsoft Access / VBA

append last entry from one table to another table

by: Sakakini | last post by:

How can I append last entry from one table to another table???

Microsoft Access / VBA

How to insert new records from one table to another table

by: xoozlez | last post by:

Hi there, How do I insert new records from a dbo table to another table? This is what I have: 1 dbo_company 1 Member (table) I made a query in dbo_company with the criteria I only want to...

Microsoft Access / VBA

Updating table from another table problem..

by: tomash | last post by:

Hi! I ve got two tables in Access 2007. I want to update a field of DataTable from another table, DataSumTable when two of their fields equals. ( the fields : Name and Period) I tried this...

Microsoft Access / VBA

How to migrate comma separated values from one table to another table?

by: anil2083 | last post by:

How to migrate the comma separated values from one table to another table? suppose we have table i.e XYZ and we have comma separated values in few columns i.e( column_name and values are...

MySQL Database

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

SQL Create Dynamic Table View From Another Table

Similar topics