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
