Sql server dynamic rod with dynamic fields type
I need to create a dynamic pivot table in Sql Server 2005 and I found some great answers on how to do this, but I have additional implementation requirements: the 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 output:
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 different keys and about 500 different attributes.
Here are 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 individual key in the MyData table, I have to create a record with the key as the first field, and then for each individual attribute name, I have to find the appropriate type, size and precision in the Attributes table, output the field named [AttributeName] the correct type, and apply [ AttributeValue] to the appropriate type .
Can this be done with Pivot? Or maybe some kind of FOR XML clause trick might help to consider the field type? I don't know, besides a special stored procedure for generating a lot of dynamic sql, but that would not be easy to recycle and would probably be difficult to maintain and inefficient.
source to share
I found this working solution.
It is not complete (missing varchar size and precision of numeric types), but it is enough to get the idea. Casting cannot be done on the pivot section, and of course cannot work when querying the original table as the types are mixed. The solution I found was to add the listing to the dynamic column list, creating a simpler list (field names only) for the pivot point and a longer one (including casting) for the main selection.
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
source to share