|
|
1
John Cappelletti
8 年前
在parse/split函数的帮助下,该函数返回序列和值。我们首先取消透视您的数据,然后执行简单的透视。
我应该补充一点,替换
[dbo]。[tvf str parse]()是一件小事。
with a subquery.
示例
declare@yourtable table(id int,sourcetechattributesname varchar(max),sourcetechattributesvalue varchar(max))。
插入到@yourtable值中
(1,'mediaformat,framerate,dropframe,startsmpte','96,29.97,false,00:00:00:00')
,(2,'mediaformat,nativeframerate,actiontype,viewportdisplayformat,viewportapectratio,framerate,width,height,scantype,field优势,dropframe,nativefieldorder,cadencepattern,numberofaudiochannels,numberofaudiotracks,startsmpte,duration',645,23.98,live action,anamorphic,1.78:1(16x9),23.98,1920,1080,progressive,lower field首先,错误,渐进,2-2,12,12,00:59:35:00,1507.380875')
选择*
从(
选择A.ID
,B. *
来自@yourtable a
交叉应用(select item=concat('src',b1.retval)
,值=b2.retval
来自[dbo]。[tvf str parse](sourceTechAttributesName,,')b1
加入[dbo]。[tvf str parse](sourcetechattributesValue,,')b2
在b1.retseq=b2.retseq上
B
SRC
透视(项目的最大值)([srcMediaFormat]、[srcFrameRate]、[srcDropFrame]、[srcWidth]、[srcHeight]、[srcCodec]、[srcDuration])pvt
返回
为了帮助可视化,子查询会生成
分析/拆分函数if interested
create function[dbo]。[tvf str parse](@string varchar(max),@delimiter varchar(10))。
返回表
AS
返回(
选择retseq=row_number()over(order by(select null))。
,retval=ltrim(rtrim(b.i.value('(../text())[1],'varchar(max)'))
从(select x=cast('<x>'+replace((select replace(@string,@delimiter,'§§§)作为[*]用于XML路径(“”),'
交叉应用x.nodes(“x”)作为b(i)
;
--从[dbo]中选择*。[tvf str parse]('dog,cat,house,car',',')
--从[dbo]中选择*。[tvf str parse]('this,is,<test>,for,<&>'、'、')
我要补充一点,这是一个小问题,以取代[dbo].[tvf-Str-Parse]()使用子查询。
例子
Declare @YourTable table (ID int,SourceTechAttributesName varchar(max),SourceTechAttributesValue varchar(max))
Insert Into @YourTable values
(1,'MediaFormat, FrameRate, DropFrame, StartSmpte','96, 29.97, False, 00:00:00:00')
,(2,'MediaFormat, NativeFrameRate, ActionType, ViewportDisplayFormat, ViewportAspectRatio, FrameRate, Width, Height, ScanType, FieldDominance, DropFrame, NativeFieldOrder, CadencePattern, NumberOfAudioChannels, NumberOfAudioTracks, StartSmpte, Duration','645, 23.98, Live Action, Anamorphic, 1.78:1 (16x9), 23.98, 1920, 1080, Progressive, Lower Field First, False, Progressive, 2-2, 12, 12, 00:59:35:00, 1507.380875')
Select *
From (
Select A.ID
,B.*
From @YourTable A
Cross Apply (Select Item = concat('src',B1.RetVal)
,Value = B2.RetVal
From [dbo].[tvf-Str-Parse](SourceTechAttributesName ,',') B1
Join [dbo].[tvf-Str-Parse](SourceTechAttributesValue,',') B2
on B1.RetSeq=B2.RetSeq
) B
) src
Pivot (max(value) for Item in ([srcMediaFormat],[srcFrameRate],[srcDropFrame],[srcWidth],[srcHeight],[srcCodec],[srcDuration]) ) pvt
退换商品
为了帮助可视化,子查询生成
分析/拆分函数(如果感兴趣)
CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[tvf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[tvf-Str-Parse]('this,is,<test>,for,< & >',',')
|
