Sql server dynamic pivot with dynamic fields type

I need to build a dynamic pivot table on Sql Server 2005 and I found several excellent answers on how to do it, but I have an extra requirement to implement: dynamic field type.

This is my data structure

CREATE TABLE [dbo].[MyData](
[Key] [char](12) NOT NULL,
[AttributeName] [char](3) NOT NULL,
[AttributeValue] [char](40) NOT NULL)

This is the definition of types.

CREATE TABLE [dbo].[Attributes](
[AttributeName] [char](3) NOT NULL,
[CastTo] [char](10) NOT NULL,
[Size] int NULL,
[Precision] int NULL)

and this is the expected result:

KEY  | 001 (of type found in tab Attributes) | 002          | 003    |004         |...
---------------------------------------------------------------------------------------
k1   | I am a Varchar                          |  12345.789 | 0 (bit)| 2014-10-02 |...

MyData can contain about 100.000 distinct keys and about 500 distinct AttributeName.

Here is some sample data:

INSERT [MyAttributes] ([AttributeName], [CastTo], [Size], [Precision]) VALUES (N'001', N'varchar   , 10, NULL)
GO
INSERT [MyAttributes] ([AttributeName], [CastTo], [Size], [Precision]) VALUES (N'002', N'int       ', NULL, NULL)
GO
INSERT [MyAttributes] ([AttributeName], [CastTo], [Size], [Precision]) VALUES (N'003', N'datetime  ', NULL, NULL)
GO
INSERT [MyAttributes] ([AttributeName], [CastTo], [Size], [Precision]) VALUES (N'004', N'decimal   ', 10, 4)
GO
INSERT [MyData] ([Key], [AttributeName], [AttributeValue]) VALUES (N'k1', N'001', N'abcd                                    ')
GO
INSERT [MyData] ([Key], [AttributeName], [AttributeValue]) VALUES (N'k1', N'002', N'111                                     ')
GO
INSERT [MyData] ([Key], [AttributeName], [AttributeValue]) VALUES (N'k1', N'003', N'20150102                                ')
GO
INSERT [MyData] ([Key], [AttributeName], [AttributeValue]) VALUES (N'k1', N'004', N'12345.1                                 ')
GO
INSERT [MyData] ([Key], [AttributeName], [AttributeValue]) VALUES (N'k2', N'001', N'efgh                                    ')
GO
INSERT [MyData] ([Key], [AttributeName], [AttributeValue]) VALUES (N'k2', N'002', N'222                                     ')
GO
INSERT [MyData] ([Key], [AttributeName], [AttributeValue]) VALUES (N'k3', N'003', N'20121212                                ')
GO

For each distinct Key in table MyData I must create a record with Key as first field, then for each distinct AttributeName I must look up on Attributes table the proper type, size and precision, output a field named [AttributeName] of the proper type and cast [AttributeValue] to the proper type.

Can this be done using Pivot? Or maybe some trick with the FOR XML clause could help to consider the field type? I have no idea beside a custom Stored procedure to generate a lot of dynamic sql but it would not be easily recyclable and probably would be hard to maintain and inefficient.

I found this working solution.

It is not complete (missing size of varchar and precision of numeric types) but it is enough to get the idea. The casting cannot be done on the pivot section and of course cannot work when querying the source table because types are mixed. The solution I found is to add casting to the dynamic column list, creating a simpler list (field names only) for the pivot and a longer (including cast) for the main select.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX), @ColumnsShort nVarChar(MAX);
SET @columnsShort = '';
SELECT @columnsShort  = @columnsShort + N', p.' + QUOTENAME(AttributeName)
  FROM (SELECT distinct p.AttributeName FROM MyData AS p
  INNER JOIN MyAttributes AS o
ON rtrim(p.AttributeName) = rtrim(o.AttributeName)
  ) AS x;

SET @columns = '';
SELECT @columns  = @columns + N', ' + attr
  FROM (SELECT distinct 'CAST(' + QUOTENAME(p.AttributeName) + ' AS ' + o.CastTo + ') AS ' + QUOTENAME(p.attributename) as attr FROM MyData AS p
  INNER JOIN MyAttributes AS o
ON rtrim(p.AttributeName) = rtrim(o.AttributeName)
  ) AS x;

SET @sql = N'
SELECT  [Key], ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT [key], p.AttributeName, attributevalue
   FROM dbo.MyData AS p
   INNER JOIN dbo.MyAttributes AS o
   ON rtrim(p.AttributeName) = rtrim(o.AttributeName)
) AS j
PIVOT
(
 max(AttributeValue) FOR AttributeName IN ('
  + STUFF(REPLACE(@columnsshort, ', p.[', ',['), 1, 1, '')
  + ') 
) AS p;';
PRINT @sql;

execute sp_executesql @sql