SQL Server : convert multiple rows in single column

Question

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 :

I always know the number of rows and columns that the file has.
I am using SQL Server 2016
I do not have sysadmin rights

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

Thanks!

You have a csv and you want to turn it into a table with more than 600 columns??? — Sean Lange
– Sean Lange, Commented Dec 19, 2019 at 19:26
If its just a one time thing why don't you edit the csv instead? Transposing will be much easier — CR7SMS
– CR7SMS, Commented Dec 19, 2019 at 19:29
Like Sean, I doubt you really need 600 columns. Personally, though, I think you'd be better off doing to task outside of SQL; if you must. You could easily open the file in Excel and copy and paste transform the data. — Thom A
– Thom A ♦, Commented Dec 19, 2019 at 19:30
There is a lot of CSV files that will need to pass by that query and those CSV files will be modified sometimes. I would like to be able to reimport them in a SQL table with this. — Francis Henry
– Francis Henry, Commented Dec 19, 2019 at 19:32

John Cappelletti · Accepted Answer · 2019-12-19 21:17:11Z

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

Thanks for your answer! Really appreciated! I am having some small issues with it but I think I can work with this! It looks quite long for 73 Recipes / 868 rows (3:30min)
To complicate stuff a little bit, there are null values sometimes like this: Recipe,Recipe, ,1 a,b c,d ,9
@FrancisHenry Happy it helped. 3.5 minutes seems long to me, for example, I have a similar process 7,000 equities and 140 elements ... 6.8 seconds on my laptop.
@FrancisHenry XML really does not like NULLS ... I even tried For XML Path(''),ELEMENTS XSINIL but that did not help, I would suggest replacing ,, with ,NULL, for example Set @S=replace(@S,',,',',NULL,')

Collectives™ on Stack Overflow

SQL Server : convert multiple rows in single column

1 Answer 1

15 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Related