AliIybar: Created page with "Category:SQL == Target == I have two tables. # keeps structural data about a dynamically created table # keep data of dynamically created table. I need to get 3 column..."

2016-11-22T10:53:39Z

Created page with "Category:SQL == Target == I have two tables. # keeps structural data about a dynamically created table # keep data of dynamically created table. I need to get 3 column..."

New page

[[Category:SQL]]
== Target ==
I have two tables.
# keeps structural data about a dynamically created table
# keep data of dynamically created table.

I need to get 3 columns
# FieldName
# FieldType
# Data

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

== Stored Procedure ==
<pre class="brush:sql;">
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

</pre>

Using data fields as column names in SQL - Revision history

AliIybar: Created page with "Category:SQL == Target == I have two tables. # keeps structural data about a dynamically created table # keep data of dynamically created table. I need to get 3 column..."