Dynamic table generation

Question

Dynamic table generation

Mike-159444

Newbie

Points: 8
More actions
July 12, 2005 at 5:34 pm

#67215

I know somebody out there can do this in their sleep but I've been stumped all day. I have 4 tables representing my web-based documentation system. I have the data manipulation for the end-user all sorted. Web forms are built on the fly based on the template the user selects. Then the form iterates through the fields represented to strip the strings out of the textboxes and insert them in another table. What I can't seem to figure out is how to come up with a query that will give me a document instance with its fields as column headings and its values as the only value in each column.
A document (MMWord) is represented by the fields (MMWordTemplateField) tied to a template (MMWordTemplate) and the values (MMWordValue) inserted in the fields.
What I want from the following related tables, given a distinct dbo.MMWord.itemID, is this (in order to process a report using MS Reporting Services where the report is called by the webform by the value expressed in MMWordTemplate.ReportName:
Table (
[itemID] // passed in as parameter
[Job_Number] // from matching record in MMWord
[Field 0] // First field in MMWordTemplate where MMWordTemplate.itemID = MMWord.TemplateID
...
...
...
[Field N] // Where N = count(itemID) from MMWordTemplateField where MMWordTemplateField.itemID = MMWord.TemplateID
And then of course I want the values in each of the corresponding value rows tied to the fields by MMWordValue.FieldID

Below is the existing schema
CREATE TABLE [dbo].[MMWord] (
[itemID] [int] IDENTITY (1, 1) NOT NULL ,
[TemplateID] [int] NULL ,
[Job_Number] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[isDeleted] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MMWordTemplate] (
[itemID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Thumbnail] [image] NULL ,
[ReportName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[MMWordTemplateField] (
[itemID] [int] IDENTITY (1, 1) NOT NULL ,
[TemplateID] [int] NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Height] [int] NULL ,
[Width] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MMWordValue] (
[itemID] [int] IDENTITY (1, 1) NOT NULL ,
[MMWordID] [int] NULL ,
[FieldID] [int] NULL ,
[FieldValue] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Thanks for your help!
--Mike

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Just for clarity. Can you post some sample data for each table and the results you wish to get from the query. It's still a little hard to figure out what you need.

Mike-159444 Newbie Points: 8 More actions · Answer 2

Thanks Remi,

But I found my solution. Not what I'd asked for, rather the realization that at runtime I already know half of the picture and can get just what I need from the values table by way of the WordID and FieldID values. Sorry to have bothered everyone - I'll be taking this post down after I see that you've read this.

--Mike