CODEWITHSUNDEEP
sql-servercsv
Francis Henry
Francis Henry

Reputation: 13

SQL Server : convert multiple rows in single column

I am running into an issue with T-SQL code. There is a CSV file that I need to import and transform into a SQL Server table. The problem is that the CSV file is not correctly format and looks like this:

Recipe,Recipe,Recipe,Recipe,...
0,1,3,4,...
Data1,Data2,Data3,Data4,...
...

The final result would need to be at least like this:

Recipe,0,Data1,...
Recipe,1,Data2,...
Recipe,3,Data3,...
Recipe,4,Data4,...
...

I have used FOR XML PATH to get all rows into one single string but I did not end up with anything good.

The information I have :

Any help to show me the right path would be greatly appreciated!

Thanks!

Upvotes: 0

Views: 597

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

Example

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

Returns

Str
Recipe,0,Data1
Recipe,1,Data2
Recipe,3,Data3
Recipe,4,Data4

The Function if Interested

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)
);

EDIT - OPTION WITHOUT FUNCTION

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

Edit JSON OPTION -- Correcting for Double Quotes

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

Returns

Str
Recipe,1,Data1
Recipe,,Data2     -- null (2 is missing
Recipe,3,Data"3   -- has double quote
Recipe,4,Data4

Upvotes: 1

Related Questions