Sql server动态转轴与动态字段类型 [英] Sql server dynamic pivot with dynamic fields type

问题描述

我需要在Sql Server 2005上构建一个动态数据透视表，我发现了几个很好的答案，但是我有一个额外的要求来实现：动态字段类型

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.

这是我的数据结构

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

这是类型的定义。

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

这是预期的结果：

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

MyData可以包含约100.000个不同的键和约500个不同的AttributeName

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

以下是一些示例数据：

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

对于表MyData中的每个不同的键，我必须使用Key作为第一个字段创建一个记录，然后对于每个不同的AttributeName，我必须查找Attributes表的正确的ty pe，大小和精度，输出一个名为[AttributeName]的正确类型和转换[AttributeValue]到适当类型的字段。

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.

可以这是使用数据透视？或者可能有一些使用FOR XML子句的技巧可以帮助考虑字段类型？我不知道在自定义存储过程旁边生成大量的动态SQL，但是它不会容易回收，可能难以维护和低效。

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.

它不完整（缺少varchar的大小和数字类型的精度），但是足够得到的想法。
在枢轴部分无法完成转换，当然，由于类型混合，查询源表时无法正常工作。
我找到的解决方案是将动态列添加到动态列列表中，为主要选择创建一个更简单的列表（仅字段名称），并为主选择创建一个较长的（包括转换）。

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

这篇关于Sql server动态转轴与动态字段类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！