当标志allow 1时，如何将select query split temp值构建为两列，一列为数字，另一列为文本？

n6lpvg4x 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(298)

我正在处理sql server 2012的查询。我有一个问题：我不能建立选择
当临时表#nonparametric中的行具有allow=1标志时，查询将列临时值拆分为两列，
当标志allow=1时，它必须将列temp值从非参数拆分为两列。
假设列temp值的值为50.40 kg，它必须拆分为两列
第一列带有数字，因此它将具有50.40，并且它将与参数化名称相同。
第二列带有文本，因此它将具有kg，并且它将与parametric+“units”同名。
意思是名称将是parametricunit。
我需要构建一个查询，当flag allow=1时，它将此内容拆分为两列。

create table #nonparametricdata
(
PART_ID nvarchar(50) ,
CompanyName  nvarchar(50),
PartNumber nvarchar(50),
DKFeatureName nvarchar(100),
Tempvalue nvarchar(50),
FlagAllow bit
)

insert into #nonparametricdata
values
('1222','Honda','silicon','package','15.50Am',0),
('1900','MERCEIS','GLASS','family','90.00Am',1),--Build select query split data because FlagAllow=1
('5000','TOYOTA','alominia','source','70.20kg',0),
('8000','MACDA','motor','parametric','50.40kg',1),----Build select query split data because FlagAllow=1
('8900','JEB','mirror','noparametric','75.35kg',0)

create table #FinalTable
(
DKFeatureName  nvarchar(50),
DisplayOrder  int
)

insert into #FinalTable (DKFeatureName,DisplayOrder) 
values 
('package',3),
('family',4),
('source',5),
('parametric',2),
('noparametric',1)

我的尝试如下：

DECLARE @SelectqueryData NVARCHAR(MAX)
   SELECT
   @SelectqueryData = STUFF(
    (
        SELECT ', ' +  case when B.FlagAllow = 1 then '['+A.DKFeatureName+'],['+A.DKFeatureName+'Unit]' else quotename(A.DKFeatureName) end
        FROM #FinalTable A
        join  (Select distinct DKFeatureName,FlagAllow 
   From #nonparametricdata 
 ) B on A.DKFeatureName=B.DKFeatureName

        ORDER BY DisplayOrder
        FOR XML PATH ('')
    ),1,2,''
)
select @SelectqueryData
--select @SelectqueryData from table

预期结果是：

[noparametric], [parametric]--QueryGetNumber,[parametricUnit]--QueryGetUnitOfMeasure
, [package], [family]--QueryGetNumber,[familyUnit]--QueryGetUnitOfMeasure, [source]

当在上面进行查询时，它必须以图像形式给出结果（仅用于解释）：

sql stored-procedures dynamic-sql tsql sql-server-2012

来源：https://stackoverflow.com/questions/62480447/how-to-build-select-query-split-temp-value-to-two-column-one-per-number-and-anot

1条答案

按热度按时间

u4dcyp6a1#

你在寻找一个动态的支点
例子

DECLARE @SelectqueryData NVARCHAR(MAX)
SELECT  @SelectqueryData = STUFF( (
           SELECT ', ' +  case when B.FlagAllow = 1 then '['+A.DKFeatureName+'],['+A.DKFeatureName+'Unit]' else quotename(A.DKFeatureName) end
           FROM #FinalTable A
           join  (Select distinct DKFeatureName,FlagAllow 
                   From #nonparametricdata 
                  ) B on A.DKFeatureName=B.DKFeatureName
           ORDER BY DisplayOrder
           FOR XML PATH ('')
         ),1,2,''
)

Declare @SQL varchar(max) = '
Select *
 From  (
        Select A.Part_ID 
              ,A.PartNumber
              ,A.CompanyName
              ,B.*
         From  #nonparametricdata A
         Cross Apply ( values ( DKFeatureName         ,case when FlagAllow=1 then left(TempValue,patindex(''%[A-Z]%'',TempValue+''A'')-1) else TempValue end ) 
                             ,( DKFeatureName+''Unit'',case when FlagAllow=1 then substring(TempValue,patindex(''%[A-Z]%'',TempValue+''A''),10) else null end ) 
                     ) B(Item,Value)
       ) src
 Pivot (max(value) for Item in ('+@SelectqueryData+') ) pvt
'
--Print @SQL
Exec(@SQL)

退货