首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Server :转换单列中的多行

问Server :转换单列中的多行
EN

Stack Overflow用户
提问于 2019-12-19 19:12:51
回答 1查看 381关注 0票数 0

我遇到了T代码的问题.我需要导入CSV文件并将其转换为Server表。问题是CSV文件格式不正确,如下所示:

代码语言:javascript
复制
Recipe,Recipe,Recipe,Recipe,...
0,1,3,4,...
Data1,Data2,Data3,Data4,...
...

最后的结果至少应该是这样:

代码语言:javascript
复制
Recipe,0,Data1,...
Recipe,1,Data2,...
Recipe,3,Data3,...
Recipe,4,Data4,...
...

我使用XML路径将所有行都放在一个字符串中,但是我没有得到任何好的结果。

我掌握的信息:

我总是知道该文件的行数和列数。我使用的是Server 2016

  • ,没有sysadmin权限

任何帮助,以显示我正确的道路,将非常感谢!

谢谢!

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-12-19 19:31:23

示例

代码语言:javascript
复制
Declare @S varchar(max) = 'Recipe,Recipe,Recipe,Recipe
0,1,3,4
Data1,Data2,Data3,Data4'

;with cte as (
    Select CN=A.RetSeq
          ,RN=B.RetSeq
          ,Value=B.RetVal
     From  [dbo].[tvf-Str-Parse](@S,char(13)+char(10)) A
     Cross Apply [dbo].[tvf-Str-Parse](A.RetVal,',') B
)
Select Str = Stuff((Select ',' +Value From cte Where RN=A.RN Order By CN For XML Path ('')),1,1,'') 
 From (Select Distinct RN from cte) A
 Order By A.RN

返回

代码语言:javascript
复制
Str
Recipe,0,Data1
Recipe,1,Data2
Recipe,3,Data3
Recipe,4,Data4

如果感兴趣的话,函数

代码语言:javascript
复制
CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = row_number() over (order by 1/0)
          ,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)
);

编辑-没有函数的选项

代码语言:javascript
复制
Declare @S varchar(max) = 'Recipe,Recipe,Recipe,Recipe
0,1,3,4
Data1,Data2,Data3,Data4'

;with cte as (
    Select CN=A.RetSeq
          ,RN=B.RetSeq
          ,Value=B.RetVal
     From  (
            Select RetSeq = row_number() over (order by 1/0)
                  ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
            From  (Select x = Cast('<x>' + replace((Select replace(@S,char(13)+char(10),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
            Cross Apply x.nodes('x') AS B(i)
           ) A
     Cross Apply (
                    Select RetSeq = row_number() over (order by 1/0)
                          ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
                    From  (Select x = Cast('<x>' + replace((Select replace(A.RetVal,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                    Cross Apply x.nodes('x') AS B(i)
                 )  B
)
Select Str = Stuff((Select ',' +Value From cte Where RN=A.RN Order By CN For XML Path ('')),1,1,'') 
 From (Select Distinct RN from cte) A
 Order By RN

编辑JSON选项--纠正双引号

代码语言:javascript
复制
Declare @S varchar(max) = 'Recipe,Recipe,Recipe,Recipe
1,,3,4
Data1,Data2,Data"3,Data4'


;with cte as (
Select CN    = A.[key]
      ,RN    = B.[Key]
      ,Value = replace(B.Value,'||','"')
 From OpenJSON('["'+replace(replace(@S,'"','||'),char(13)+char(10),'","')+'"]')  A
 Cross Apply ( 
               Select *
                From OpenJSON('["'+replace(A.Value,',','","')+'"]')  
             ) B
)
Select Str = Stuff((Select ',' +Value From cte Where RN=A.RN Order By CN For XML Path ('')),1,1,'') 
 From (Select Distinct RN from cte) A
 Order By RN

返回

代码语言:javascript
复制
Str
Recipe,1,Data1
Recipe,,Data2     -- null (2 is missing
Recipe,3,Data"3   -- has double quote
Recipe,4,Data4
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/59415659

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档