Difference between revisions of "Using data fields as column names in SQL"

Latest revision as of 11:53, 22 November 2016

Target

I have two tables.

1. keeps structural data about a dynamically created table
2. keep data of dynamically created table.

I need to get 3 columns

1. FieldName
2. FieldType
3. Data

So I need to use field name which I read from first table to pull data from second table

Stored Procedure

USE [rmbc_EFormsLibrary]
GO
/****** Object:  StoredProcedure [dbo].[rmbc_FormBuilder_FormDataGet]    Script Date: 22/11/2016 10:47:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		Ali Iybar
-- Create date: 22/11/2016
-- Description:	Returns form data with Field Names and types
-- =============================================
ALTER PROCEDURE [dbo].[rmbc_FormBuilder_FormDataGet]
	@FormMainId			NVARCHAR(100),
	@FormDataId			NVARCHAR(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
DECLARE @sql nvarchar(max) = '';
-- =============================================
-- GET STATIC DATA FIRST
-- StartDateTime
-- =============================================
SELECT @sql = @sql + 'SELECT ''StartDate'' AS FieldName, ''DATETIME'' AS DataType, convert(varchar, D.StartDate, 121) AS Data '
+ CHAR(10)
+ 'FROM tblFB'+ convert(VARCHAR,M.DataTableName) +' D WHERE D.Id = ''' + @FormDataId + '''
 UNION'+ char(10)
FROM tblFormMain M 
WHERE M.Id =  @FormMainId 
-- =============================================
--Status
-- =============================================
SELECT @sql = @sql + 'SELECT ''Status'', ''INTEGER'', convert(varchar, D.Status) AS Data '
+ CHAR(10)
+ 'FROM tblFB'+ convert(VARCHAR,M.DataTableName) +' D WHERE D.Id = ''' + @FormDataId + '''
 UNION'+ char(10)
FROM tblFormMain M 
WHERE M.Id =  @FormMainId 
-- =============================================
--Starter
-- =============================================
SELECT @sql = @sql + 'SELECT ''Starter'', ''NVARCHAR(50)'', convert(varchar, D.Starter) AS Data '
+ CHAR(10)
+ 'FROM tblFB'+ convert(VARCHAR,M.DataTableName) +' D WHERE D.Id = ''' + @FormDataId + '''
 UNION'+ char(10)
FROM tblFormMain M 
WHERE M.Id =  @FormMainId 
-- =============================================
-- GET DYNAMIC DATA
-- =============================================

SELECT @sql = @sql + 'SELECT E.FieldName, T.DataType, convert(varchar, D.' + convert(VARCHAR,E.FieldName) + ', 121) AS Data '
+ CHAR(10)
+ 'FROM tblFormMain M 
INNER JOIN tblFormSection S ON S.tblFormMainId = M.Id
INNER JOIN tblFormElement E ON E.tblFormSectionId = S.Id
INNER JOIN tblFormFieldType T ON T.Id = E.tblFormFieldTypeId
INNER JOIN tblFB'+ convert(VARCHAR,M.DataTableName) +' D ON D.Id = ''' + @FormDataId + '''
WHERE M.Id = '''+ @FormMainId + '''
AND E.FieldName = ''' + convert(varchar,E.FieldName) +'''
 UNION'+ char(10)
FROM tblFormMain M 
INNER JOIN tblFormSection S ON S.tblFormMainId = M.Id
INNER JOIN tblFormElement E ON E.tblFormSectionId = S.Id
INNER JOIN tblFormFieldType T ON T.Id = E.tblFormFieldTypeId
WHERE M.Id =  @FormMainId 
;

SET @sql = LEFT(@sql, LEN(@SQL) - 6)
--  print @sql;
exec sp_executesql @sql;


END