sql server 2008基于序号的动态数据透视 [英] sql server 2008 dynamic pivot based on ordinal value

问题描述

我有一个UDF，可以用空格分隔字符串.当函数在每个字符串中查找空格并进行拆分时会在其间循环，因此将序数与拆分后的字符串一起插入结果表的自己列中.我已经着眼于此序号，但是这些数字当然会根据字符串而变化.我需要查询这些序数并在我的数据透视图中使用它们.有人可以告诉我如何使用动态sql吗?我在不同的论坛上看到过示例，但我认为这可能比我所看到的更为简单.我的程序如下.如果有人需要UDF，我也会将其发布.

I have a UDF that splits a character string delimited by a space. As the function loops through each string if it finds a space and makes a split an ordinal number is inserted into the results table in its own column along with the split string. I have pivoted on this ordinal number but of course these numbers change based on the character string. I need to query these ordinal numbers and use them in my pivot. Could someone show me how this could be done with dynamic sql? I have seen examples on different forums but I think this could be more simple than what I've seen. My proceedure is below. If someone needs the UDF I'll post it as well.

--The original data is pulled from column "DataString" in "MyData" table.
--After declaring the UDF split function the function is used and the results are
--inserted into a table called "ScriptResult" with this query
SELECT * 
INTO ScriptResult
FROM    
    MyData x    
    CROSS APPLY    
    dbo.Split (x.DataString, ' ') S

Where rtrim(s.StringValue) != '' 

--Then the table is pivoted on column Ordinal from table ScriptResult to summarize the data

Select *
INTO ScriptPivot
From ScriptResult
pivot (max (StringValue) for Ordinal in ([1], [2], [3], [5], [7], [8], [9], [10], [11]))
as   PivotResult 

示例输入:

R 456 ACCOUNT 56779900 23499000800973983989883 56 99750927     890-0983
Y 123M 120 M/Y JOHN DOE E 5678873940000056 000000 0003456      678-7898
U 06           000000 000567 000000000000000000M688399000      789-8388
H 120                                                          785-7848
R 456          0000000000000000000006578 786936689663   DTY578   568-7890

我正在获取一个旧的平面文件，并将其重新设计为sql.问题是创建这些文件的批处理过程只有120个字符行而没有规范化等.我正在尝试将它们放入Sql Server.我相信我可以使用导入向导更轻松地完成此操作，但是我需要Stor.Proceedure，因为这些单位仍在使用中，并且必须每天进行. 期望的结果:我拥有的数据透视表是我需要的结果，但是我只需要能够对所有这样得到的数据字符串使用此数据，而且它们都不统一，因此序数有所不同. 但这就是它的外观...

I'm taking an old flat file and de-enginering it into sql. The problem is the batch process that creates these files just has 120 character rows with no normalization etc. I'm trying to get them into Sql Server. I relize i could do this easier with an import wizard but I need a Stor.Proceedure because these flats are still being used and this will have to be done on a daily basis. Desired Result: The pivot table I have is the result I need but I just need to be able to use this for all data strings I get like this and none of them are uniform so the ordinal numbers differ. But this is how it should look...

1---2---3-------5--------7----------8-------------9--10-----------11   
R   456 ACCOUNT 56779900 2349900080 0973983989883 56 99750927     890-0983

这是冒险作品数据库中的示例动态数据透视表... 宣告@PivotColumnHeaders VARCHAR(MAX) SELECT @PivotColumnHeaders = 合并( @PivotColumnHeaders +'，['+ cast(名称为varchar)+']'， '['+ cast(名称为varchar)+']' ) 从Sales.SalesTerritory

Here is an example dynamic pivot table from the adventure works DB... DECLARE @PivotColumnHeaders VARCHAR(MAX) SELECT @PivotColumnHeaders = COALESCE( @PivotColumnHeaders + ',[' + cast(Name as varchar) + ']', '[' + cast(Name as varchar)+ ']' ) FROM Sales.SalesTerritory

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
  SELECT *
  FROM (
    SELECT
      YEAR(H.OrderDate) [Year],
      T.Name,
      H.TotalDue
    FROM Sales.SalesOrderHeader H
    LEFT JOIN Sales.SalesTerritory T
      ON H.TerritoryID = T.TerritoryID
  ) AS PivotData
  PIVOT (
    SUM(TotalDue)
    FOR Name IN (
      ' + @PivotColumnHeaders + '
    )
  ) AS PivotTable
'

EXECUTE(@PivotTableSQL)

我能以某种方式量身定制吗?

Could I somehow tailor this for my use?

推荐答案

如果您要创建一个名为Ordinals的表(或任何您想要的表)，该表包含足以覆盖可能达到的最大序数的数字，则此可能对您有用:-

If you were to have a table called Ordinals (or whatever you want) that contains numbers sufficient to cover the maximum number of ordinals you might attain, this may work for you:-

declare @ordList varchar(max);
select @ordList = stuff((select ', [' + rtrim(ordinal) + ']' as [text()] 
from ( 
    select distinct convert(varchar,ordinal) ordinal from Ordinals 
) ords for xml path('')),1,1,'')

exec ('select * from ( select ' + @ordList + ' from dataTable) tbl pivot (max(stringValue) FOR [1] in (' + @ordList + ')) PVT' ) end

这与AW示例几乎相同

这篇关于sql server 2008基于序号的动态数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！