Dynamic casting within dynamic SQL

Question

I am attempting to create a stored procedure that would return a result set with a variable number of columns (defined in tblHeaderDefinition) of variable types (also in tblHeaderDefinition). The values for these columns are stored as VARCHAR in a separate table, which is INNER JOINED to the definition table.

A few different applications (in C#, VBA, etc) bring in this data and use it in different ways, but when I bring this table in, my columns are all type VARCHAR, same as the value column. I want to cast them in SQL to the appropriate type and have the apps look at the type property of the column to determine how to handle the column, instead of bringing in the definition table to each of the individual apps and have each app cast to the correct type.

The query below returns the flat table that I am looking for, but I can't think of a way to use dynamic casting in this instance.

DECLARE @collist NVARCHAR(MAX)
SET @collist = stuff((SELECT DISTINCT ',' + QUOTENAME(ColumnName) 
            FROM tblHeaderDefinition
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

DECLARE @q NVARCHAR(MAX)
SET @q = '
    SELECT * 
    FROM (
        SELECT ColumnName, Value
            FROM (
                SELECT  tblHeaderDefinition.pkHeaderDefinitionID
                    , tblHeaderDefinition.ColumnName
                    , tblHeaderDefinition.ColumnType
                    , tblHeaderValue.Value
                FROM    tblHeaderValue 
                INNER JOIN  tblHeaderDefinition ON tblHeaderValue.fkHeaderDefinitionID = tblHeaderDefinition.pkHeaderDefinitionID
        ) AS x
    ) AS source
    pivot (
        max(Value)
        FOR ColumnName IN (' + @collist + ')
    ) AS pvt
'

EXEC (@q)

test

Tables I am working with:

CREATE TABLE [dbo].[tblHeaderDefinition](
    [pkHeaderDefinitionID] [int] IDENTITY(1,1) NOT NULL,
    [ColumnName] [varchar](256) NULL,
    [ColumnType] [varchar](256) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tblHeaderValue](
    [pkHeaderValueID] [int] IDENTITY(1,1) NOT NULL,
    [fkHeaderDefinitionID] [int] NULL,
    [fkHeaderID] [int] NULL,
    [Value] [varchar](256) NULL
) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[tblHeaderDefinition] ON 
GO
INSERT [dbo].[tblHeaderDefinition] ([pkHeaderDefinitionID], [ColumnName], [ColumnType]) VALUES (1, N'ColIntTest', N'INT')
GO
INSERT [dbo].[tblHeaderDefinition] ([pkHeaderDefinitionID], [ColumnName], [ColumnType]) VALUES (2, N'ColVarCharTest', N'VARCHAR(50)')
GO
INSERT [dbo].[tblHeaderDefinition] ([pkHeaderDefinitionID], [ColumnName], [ColumnType]) VALUES (3, N'ColRealTest', N'REAL')
GO
INSERT [dbo].[tblHeaderDefinition] ([pkHeaderDefinitionID], [ColumnName], [ColumnType]) VALUES (4, N'ColBitTest', N'BIT')
GO
SET IDENTITY_INSERT [dbo].[tblHeaderDefinition] OFF
GO

SET IDENTITY_INSERT [dbo].[tblHeaderValue] ON 
GO
INSERT [dbo].[tblHeaderValue] ([pkHeaderValueID], [fkHeaderDefinitionID], [fkHeaderID], [Value]) VALUES (1, 1, 5, N'54')
GO
INSERT [dbo].[tblHeaderValue] ([pkHeaderValueID], [fkHeaderDefinitionID], [fkHeaderID], [Value]) VALUES (2, 2, 5, N'NA-0490')
GO
INSERT [dbo].[tblHeaderValue] ([pkHeaderValueID], [fkHeaderDefinitionID], [fkHeaderID], [Value]) VALUES (3, 3, 5, N'1000.094')
GO
INSERT [dbo].[tblHeaderValue] ([pkHeaderValueID], [fkHeaderDefinitionID], [fkHeaderID], [Value]) VALUES (4, 4, 5, N'1')
GO
SET IDENTITY_INSERT [dbo].[tblHeaderValue] OFF
GO

Answer 1

You can use another variable that holds the casting of the columns:

DECLARE @collist NVARCHAR(MAX), @collist2 NVARCHAR(MAX);

SET @collist = stuff((SELECT DISTINCT ',' + QUOTENAME(ColumnName) 
            FROM tblHeaderDefinition
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @collist2 = stuff((SELECT DISTINCT ',CAST(' + QUOTENAME(ColumnName) + ' AS ' + 
                                        ColumnType + ') AS ' + QUOTENAME(ColumnName)
            FROM tblHeaderDefinition
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

DECLARE @q NVARCHAR(MAX)
SET @q = '
    SELECT ' + @collist2 + ' 
    FROM (
        SELECT ColumnName, Value
            FROM (
                SELECT  tblHeaderDefinition.pkHeaderDefinitionID
                    , tblHeaderDefinition.ColumnName
                    , tblHeaderDefinition.ColumnType
                    , tblHeaderValue.Value
                FROM    tblHeaderValue 
                INNER JOIN  tblHeaderDefinition ON tblHeaderValue.fkHeaderDefinitionID = tblHeaderDefinition.pkHeaderDefinitionID
        ) AS x
    ) AS source
    pivot (
        max(Value)
        FOR ColumnName IN (' + @collist + ')
    ) AS pvt
'

EXEC (@q)