By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

SQL Create Dynamic Table View From Another Table

P: 76
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
  1. ---------------
  2. PUBLICATIONS
  3. ---------------
  4. PublicationID (int)
  5. PublicationTypeID (int)
  6. AuthorList (varchar)
  7. Synopsis (varchar)
  8.  
  9. ------------------
  10. PUBLICATION_TYPE
  11. ------------------
  12. PublicationTypeID (int)
  13. PublicationTypeName (varchar)
  14.  
  15. ----------------------
  16. PUBLICATION_TYPE_FIELD
  17. ----------------------
  18. FieldID (int)
  19. PublicationTypeID (int)
  20. FieldType (varchar) 
  21. FieldName (varchar)
  22. Required (bit)
  23.  
  24. ------------------------
  25. PUBLICATION_INFORMATION
  26. ------------------------
  27. PublicationID (int)
  28. FieldID (int)
  29. FieldValue (varchar)
  30.  
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
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
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

Post your reply

Sign in to post your reply or Sign up for a free account.