是否可以从具有不同数据类型的键值表创建动态数据透视查询? [英] Can I create dynamic pivot query from key value table with different data types?

问题描述

我有一个键-值对表.我已经使用动态sql创建了一个脚本，该脚本可以正确地旋转表.但是，键具有不同的数据类型.有没有一种方法可以在数据透视期间或之后动态地投射键?我可以将每个键的数据类型放在相同的键值对表中，也可以将其放在可通过该键链接的单独表中.

I have a key-value pairs table. I have created a script using dynamic sql that pivots the table correctly. However, the keys have different data types. Is there a way to cast the keys during or after the pivot dynamically? I can have the data types in the same key-value pairs table for each key or in a separate table linkable by the key.

使用动态sql，因为我永远不会知道这些列.永远会有数据类型.

Dynamic sql is used because I wont always know the columns. There will always be a data type.

起始表的示例:

sampleid     key   value    datatype
------------------------------------
1001        Name   Andrew    varchar(50)
1001        Date   20150129  datetime
1002        Name   Anna      varchar(50)
1002        Date   20150129  datetime

最终结果将是这样，名称为nvarchar，日期为datetime: 该脚本是一个存储过程，可将其创建到视图中.可通过外部应用程序(例如SAS和Olive)访问该视图，这些应用程序从视图中获取数据类型.当然，这不是理想的选择，但这是我受命尝试的任务！

Final result would be this with name as nvarchar and date as datetime: The script is a stored procedure that creates this into a view. The view is being accessed via external applications like SAS and Olive which pick up the datatype from the view. Of course this isn't ideal but it is what I have been tasked with attempting!

sampleid  name      date
-----------------------------
1001     Andrew    20150129
1002     Anna      20150129

推荐答案

您可以使用动态SQL进行此操作，只需将两个新列"的单独列表作为字符串创建即可.一个列表将包含要转换为数据类型的列名，第二个列表将用于PIVOT函数.

You can do this using dynamic SQL, you'll just have to crate two separate lists of "new columns" as a string. One list will include the column names being converted to your datatype, the second will be used for the PIVOT function.

代码为:

DECLARE 
    @cols AS NVARCHAR(MAX),
    @colsConversion AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

-- get the list of [key] items for the columns used in the PIVOT
select @cols 
  = STUFF((SELECT ', ' + QUOTENAME([key])
            from yourtable
            group by [key], datatype           
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- get the list of columns for the final select
-- include a conversion of the columns into the correct datatype
select @colsConversion 
  = STUFF((SELECT ', cast(' + QUOTENAME([key]) +' as '+ datatype+') as ' + QUOTENAME([key])
            from yourtable
            group by [key], datatype           
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- the converted columns go in the final select list 
-- while the other @cols are used inside the PIVOT
set @query = 'SELECT sampleid, ' + @colsConversion + ' 
            from 
            (
              select sampleid, [key], value
              from yourtable
            ) x
            pivot 
            (
               max(value)
               for [key] in (' + @cols + ')
            ) p; '

exec sp_executesql @query;

请参见带有演示的SQL提琴

这篇关于是否可以从具有不同数据类型的键值表创建动态数据透视查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！