Select the column names from one table and Corresponding data from another table

I have 2 tables:

1. ColumnDefinition : column names will store
2. ColumnData: corresponding columniD(column name) we will insert the data.

I need one select statement with column names and with data as a DataSet.( out put may also XML )

TABLES:

FormColumDefinition
------------------------------
formColumnID
formColumnDataType
formColumnName
formColumnLabel
formColumnSeqNumber (just represents how it should appear on the form).


FormData
---------------
formDataID
formRowNumber 
formColumnID
formD开发者_开发技巧ataDate (date)  - only one of the three date/int/char will have value based on the column data type.
formDataInt (int)
formDataChar (varchar)

---

If I understand you correctly, given the following:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FormData](
    [formDataID] [int] IDENTITY(1,1) NOT NULL,
    [formRowNumber] [int] NOT NULL,
    [formColumnID] [int] NOT NULL,
    [formDataDate] [datetime] NULL,
    [formDataInt] [int] NULL,
    [formDataChar] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[FormData] ON
INSERT [dbo].[FormData] ([formDataID], [formRowNumber], [formColumnID], [formDataDate], [formDataInt], [formDataChar]) VALUES (1, 1, 1, NULL, NULL, N'John')
INSERT [dbo].[FormData] ([formDataID], [formRowNumber], [formColumnID], [formDataDate], [formDataInt], [formDataChar]) VALUES (2, 1, 2, NULL, NULL, N'Private')
INSERT [dbo].[FormData] ([formDataID], [formRowNumber], [formColumnID], [formDataDate], [formDataInt], [formDataChar]) VALUES (3, 1, 3, NULL, NULL, N'123456')
INSERT [dbo].[FormData] ([formDataID], [formRowNumber], [formColumnID], [formDataDate], [formDataInt], [formDataChar]) VALUES (4, 2, 1, NULL, NULL, N'Bill')
INSERT [dbo].[FormData] ([formDataID], [formRowNumber], [formColumnID], [formDataDate], [formDataInt], [formDataChar]) VALUES (5, 2, 2, NULL, NULL, N'Captain')
INSERT [dbo].[FormData] ([formDataID], [formRowNumber], [formColumnID], [formDataDate], [formDataInt], [formDataChar]) VALUES (6, 2, 3, NULL, NULL, N'789352')
SET IDENTITY_INSERT [dbo].[FormData] OFF

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FormColumnDefinition](
    [formColumnID] [int] IDENTITY(1,1) NOT NULL,
    [formColumnDataType] [varchar](50) NOT NULL,
    [formColumnName] [varchar](50) NOT NULL,
    [formColumnLabel] [varchar](50) NOT NULL,
    [formColumnSeqNumber] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[FormColumnDefinition] ON
INSERT [dbo].[FormColumnDefinition] ([formColumnID], [FormColumnDataType], [formColumnName], [formColumnLabel], [formColumnSeqNumber]) VALUES (1, N'System.String', N'Name', N'Name', 0)
INSERT [dbo].[FormColumnDefinition] ([formColumnID], [FormColumnDataType], [formColumnName], [formColumnLabel], [formColumnSeqNumber]) VALUES (2, N'String.String', N'Rank', N'Rank', 1)
INSERT [dbo].[FormColumnDefinition] ([formColumnID], [FormColumnDataType], [formColumnName], [formColumnLabel], [formColumnSeqNumber]) VALUES (3, N'System.Int32', N'SerialNumber', N'Serial Number', 2)
SET IDENTITY_INSERT [dbo].[FormColumnDefinition] OFF

You could use a pivot command like:

SELECT formRowNumber, [Name],[Rank],[SerialNumber]
FROM
(SELECT fcd.formColumnName, fd.formRowNumber, fd.formDataChar
FROM FormColumnDefinition fcd INNER JOIN FormData fd
    ON fcd.formColumnID = fd.formColumnID) AS src
PIVOT
(
MAX(formDataChar)
FOR formColumnName IN ([Name],[Rank],[SerialNumber])
) AS pvt
ORDER BY pvt.formRowNumber

to get:

formRowNumber   Name    Rank    SerialNumber
1               John    Private 123456
2               Bill    Captain 789352

The problem with PIVOT is that you have to know the column names ahead of time. If you don't mind a little dynamic SQL, you can work around this too. Here's an example that I shamelessly stole from Andras at http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx.

DECLARE @cols NVARCHAR(2000);
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + formColumnName
                        FROM FormColumnDefinition
                        ORDER BY '],[' + formColumnName
                        FOR XML PATH('')
                      ), 1, 2, '') + ']';                

DECLARE @query NVARCHAR(4000);
SET @query = N'SELECT formRowNumber, ' + @cols + 
    'FROM (SELECT fcd.formColumnName, fd.formRowNumber, fd.formDataChar ' +
    'FROM FormColumnDefinition fcd INNER JOIN FormData fd ' +
    'ON fcd.formColumnID = fd.formColumnID) AS src PIVOT ' +
    '(MAX(formDataChar) FOR formColumnName IN ('+ @cols + ')) AS pvt ORDER BY pvt.formRowNumber;';

EXECUTE(@query);