Wednesday, April 10, 2013

How to create a dynamic table which can dynamically storing data without the need of create physical stucture tables.


DB design schema

GO
/****** Object:  Table [dbo].[Designer_Form]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Designer_Form](
    [FormID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [FormTitle] [nvarchar](150) NULL,
    [SubCategoryID] [smallint] NULL,
    [Description] [nvarchar](1150) NULL,
    [DevelopmentID] [int] NULL,
 CONSTRAINT [PK_Form] PRIMARY KEY CLUSTERED
(
    [FormID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Designer_FieldType]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Designer_FieldType](
    [FieldTypeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Type] [nvarchar](50) NOT NULL,
    [Description] [nvarchar](250) NULL,
    [Code] [varchar](2) NOT NULL,
 CONSTRAINT [PK_FieldType] PRIMARY KEY CLUSTERED
(
    [FieldTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Designer_Applicant]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Designer_Applicant](
    [ApplicantID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Phone] [varchar](50) NOT NULL,
    [Email] [varchar](50) NOT NULL,
    [IsAgreeReceiveUpdates] [bit] NOT NULL,
    [Remarks] [varchar](350) NULL,
    [DevelopmentID] [int] NULL,
    [EnquiryID] [int] NULL,
 CONSTRAINT [PK_Designer_Applicant] PRIMARY KEY CLUSTERED
(
    [ApplicantID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Designer_FormGroup]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Designer_FormGroup](
    [FormGroupID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [GroupName] [nvarchar](100) NULL,
    [Description] [nvarchar](550) NULL,
    [FormID] [int] NULL,
 CONSTRAINT [PK_FormGroup] PRIMARY KEY CLUSTERED
(
    [FormGroupID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Designer_Form_SubCategory]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Designer_Form_SubCategory](
    [FormID] [int] NOT NULL,
    [SubCategoryID] [smallint] NOT NULL,
    [CountryCode] [char](2) NULL,
 CONSTRAINT [PK_Designer_Form_SubCategory] PRIMARY KEY CLUSTERED
(
    [FormID] ASC,
    [SubCategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Designer_Field]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Designer_Field](
    [FieldID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [FieldName] [nvarchar](50) NOT NULL,
    [FormGroupID] [int] NOT NULL,
    [FieldTypeID] [int] NOT NULL,
    [FormID] [int] NOT NULL,
    [Description] [nvarchar](250) NULL,
    [IsRequiredField] [bit] NULL,
 CONSTRAINT [PK_Field] PRIMARY KEY CLUSTERED
(
    [FieldID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Designer_RepliedForm]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Designer_RepliedForm](
    [RepliedFormID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [FieldID] [int] NOT NULL,
    [Answer] [nvarchar](max) NULL,
    [ApplicantID] [int] NULL,
 CONSTRAINT [PK_RepliedForm] PRIMARY KEY CLUSTERED
(
    [RepliedFormID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Designer_FieldAttribute]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Designer_FieldAttribute](
    [FieldAttributeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [AttributeName] [nvarchar](250) NULL,
    [FieldID] [int] NULL,
 CONSTRAINT [PK_FieldAttribute] PRIMARY KEY CLUSTERED
(
    [FieldAttributeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Default [DF_Designer_Applicant_IsAgreeReceiveUpdates]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_Applicant] ADD  CONSTRAINT [DF_Designer_Applicant_IsAgreeReceiveUpdates]  DEFAULT ((0)) FOR [IsAgreeReceiveUpdates]
GO
/****** Object:  ForeignKey [FK_Field_FieldType]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_Field]  WITH CHECK ADD  CONSTRAINT [FK_Field_FieldType] FOREIGN KEY([FieldTypeID])
REFERENCES [dbo].[Designer_FieldType] ([FieldTypeID])
GO
ALTER TABLE [dbo].[Designer_Field] CHECK CONSTRAINT [FK_Field_FieldType]
GO
/****** Object:  ForeignKey [FK_Field_Form]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_Field]  WITH CHECK ADD  CONSTRAINT [FK_Field_Form] FOREIGN KEY([FormID])
REFERENCES [dbo].[Designer_Form] ([FormID])
GO
ALTER TABLE [dbo].[Designer_Field] CHECK CONSTRAINT [FK_Field_Form]
GO
/****** Object:  ForeignKey [FK_Field_FormGroup]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_Field]  WITH CHECK ADD  CONSTRAINT [FK_Field_FormGroup] FOREIGN KEY([FormGroupID])
REFERENCES [dbo].[Designer_FormGroup] ([FormGroupID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Designer_Field] CHECK CONSTRAINT [FK_Field_FormGroup]
GO
/****** Object:  ForeignKey [FK_FieldAttribute_Field]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_FieldAttribute]  WITH CHECK ADD  CONSTRAINT [FK_FieldAttribute_Field] FOREIGN KEY([FieldID])
REFERENCES [dbo].[Designer_Field] ([FieldID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Designer_FieldAttribute] CHECK CONSTRAINT [FK_FieldAttribute_Field]
GO
/****** Object:  ForeignKey [FK_Designer_Form_SubCategory_Designer_Form]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_Form_SubCategory]  WITH CHECK ADD  CONSTRAINT [FK_Designer_Form_SubCategory_Designer_Form] FOREIGN KEY([FormID])
REFERENCES [dbo].[Designer_Form] ([FormID])
GO
ALTER TABLE [dbo].[Designer_Form_SubCategory] CHECK CONSTRAINT [FK_Designer_Form_SubCategory_Designer_Form]
GO
/****** Object:  ForeignKey [FK_Designer_FormGroup_Designer_Form]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_FormGroup]  WITH CHECK ADD  CONSTRAINT [FK_Designer_FormGroup_Designer_Form] FOREIGN KEY([FormID])
REFERENCES [dbo].[Designer_Form] ([FormID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Designer_FormGroup] CHECK CONSTRAINT [FK_Designer_FormGroup_Designer_Form]
GO
/****** Object:  ForeignKey [FK_Designer_RepliedForm_Designer_Applicant]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_RepliedForm]  WITH CHECK ADD  CONSTRAINT [FK_Designer_RepliedForm_Designer_Applicant] FOREIGN KEY([ApplicantID])
REFERENCES [dbo].[Designer_Applicant] ([ApplicantID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Designer_RepliedForm] CHECK CONSTRAINT [FK_Designer_RepliedForm_Designer_Applicant]
GO
/****** Object:  ForeignKey [FK_RepliedForm_Field]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_RepliedForm]  WITH CHECK ADD  CONSTRAINT [FK_RepliedForm_Field] FOREIGN KEY([FieldID])
REFERENCES [dbo].[Designer_Field] ([FieldID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Designer_RepliedForm] CHECK CONSTRAINT [FK_RepliedForm_Field]
GO


Select query:

select * from (
SELECT   
  (SELECT Answer =
Stuff((SELECT   ', ' + Answer AS [text()] 
        FROM 
        (SELECT DISTINCT Answer, ApplicantID    FROM (SELECT  [Answer], ApplicantID    
  FROM [Designer_RepliedForm]
  where FieldID = Designer_Field.FieldID and ApplicantID = Designer_Applicant.ApplicantID) as Answer
         ) x
        For XML PATH ('')),1,1,'')  ) as Answer

, Designer_Field.FieldName, Designer_Applicant.DevelopmentID,Designer_Applicant.EnquiryID , Designer_Applicant.ApplicantID, Designer_Applicant.Name, Designer_Applicant.Email,Designer_Applicant.Phone, Designer_Applicant.Remarks
FROM         Designer_RepliedForm INNER JOIN
                      Designer_Applicant ON Designer_RepliedForm.ApplicantID = Designer_Applicant.ApplicantID INNER JOIN
                      Designer_Field ON Designer_RepliedForm.FieldID = Designer_Field.FieldID
                      ) as temp
                      pivot(
                     Max(Answer) for FieldName in ([About Me],[I would like to] ) )
                      as Answers
                      order by ApplicantID


17 comments:

Anonymous said...

Thanks for your marvelous posting! I seriously enjoyed reading it, you happen to be a great author.
I will be sure to bookmark your blog and may come back in the foreseeable future.
I want to encourage that you continue your great posts, have a
nice holiday weekend!

my blog post; kitchen cabinet

Anonymous said...

Link exchange is nothing else however it is simply placing the other
person's website link on your page at appropriate place and other person will also do similar for you.

Here is my web site bestcloudcomputingoffers.com

Anonymous said...

I don't even know how I ended up here, but I thought this post was great. I do not know who you are but certainly you are going to a famous blogger if you aren't alreаdу ;) Chеегs!


my web blog - oraculo.inf.br

Anonymous said...

Hello! I've been following your blog for some time now and finally got the bravery to go ahead and give you a shout out from Humble Tx! Just wanted to say keep up the great work!

My site: rar pass cracker

Anonymous said...

Very rapidly this website will be famous among all blog viewers, due
to it's nice posts

Also visit my blog: sturling watches for men

Anonymous said...

This article will assist the internet visitors for building up new webpage or even a weblog from
start to end.

my web site: stuhrling original

Anonymous said...

Ahaa, its nice discussion regarding this piece
of writing at this place at this webpage, I have read all
that, so at this time me also commenting at this place.


Have a look at my page :: best shared hosting companies

Anonymous said...

In the long tеrm howeveг, eхpecting anything diffeгеnt than losing abοut $3-5 fоr every $100 you put into
a slot machine іs just гidiculous. Gameplaу: The ѕtandard slot machinе rules are in plaу.
Though, there іs some of the best roulеtte stгаtegy that cοuld maіntain your сhancеѕ
of winnіng.

Look into my site ... online casinos

Anonymous said...

Hurrah! In the end I got a web site from
where I be capable of actually get valuable facts regarding my study and knowledge.



Feel free to surf to my website ... Jordan Femme

Anonymous said...

Good post! We are linking to this great content on our website.
Keep up the good writing.

Also visit my blog post ... men's stuhrling original watches

Anonymous said...

Link exchange is nothing else however it is only placing the other person's website link on your page at appropriate place and other person will also do same in favor of you.

Here is my website :: devenir riche

Anonymous said...

I love what you guys tend to be up too. This sort of clever work
and coverage! Keep up the superb works guys I've added you guys to my blogroll.

Here is my website parquet pas cher paris

Anonymous said...

Greetings! I've been following your weblog for some time now and finally got the bravery to go ahead and give you a shout out from Porter Texas! Just wanted to mention keep up the great job!

Feel free to visit my web-site comment devenir riche

Anonymous said...

Gооd ωaу of explaining, аnԁ pleаsant post tο take facts сoncerning my pгesentаtion ѕubjеct
matter, whiсh i аm going to ρrеsеnt in acаdemу.


Μy page ... devis fenetre pvc

Anonymous said...

Today, while I was at work, my sister stole my iPad and tested
to see if it can survive a thirty foot drop, just so she can
be a youtube sensation. My iPad is now destroyed and
she has 83 views. I know this is entirely off topic but I had
to share it with someone!

Feel free to visit my website - Sac Louis Vuitton Pas Cher

International Toll Free Number said...

Thanks for this article. I find it a pain to locate good quality
guidance out there when it comes to this topic. Thanks for the article!

mohd najib said...

wow...you are amazing.are you programmer?i have learn a little about code.but i'm not not expert like you.keep it up buddy.success!!!