Quebrando uma string em N colunas sem saber o número máximo de colunas

 Olá pessoal, hoje gostaria de compartilhar um script que fiz e achei bem legal. Eu tinha a seguinte situação, precisei subir um arquivo csv para o SQL para fazer uma comparação, ao fazer essa comparação optei por quebrar essas linhas em colunas realizando um pivot, até ai sem segredos, mas como em SQL nem tudo é fácil, eu não sabia a quantidade de colunas que precisava por no pivot :(, para resolver esse problema eu lembrei de uma dica da lenda do tuning o Fabiano Amorim, que explica como contar a quantidade de X caractere na string. Vou explicar abaixo.

Antes vamos montar a tabela auxiliar e já vou postar o script aqui:

use tempdb

drop table if exists teste

create table teste(

line varchar(max)

)

insert into teste values 

 ('aa,asasas,asasa,asa,sasasasa,add,dddads,dsdsds,ddsdsd')

,('skiaojhsaijsa,asasa,sasasa,sasas,as,as,a,sa,sa,sa,sa,a,a,a,sa,')

,('a,b')

declare 

 @qtd_col int

,@col varchar(max)

,@sql_batch nvarchar(max)

,@delimited char(1) = ','

,@name_col_1 varchar(50)= ' line' 

,@table_name varchar(50) = ' teste '

select @qtd_col = max(LEN(line)-len(replace(line,',',''))) from teste

;with monta_col

as

(

select ID = 1

UNION ALL

SELECT ID+1 FROM  monta_col

WHERE ID < @qtd_col

)

SELECT  @col = STRING_AGG('[COL'+rtrim(ltrim(CAST(ID AS VARCHAR(50))))+']',',') FROM monta_col

OPTION(MAXRECURSION  0 )

set @sql_batch =  'select '+@name_col_1+',' + @col +CHAR(13)

+ 'from ('+CHAR(13)

+ 'select ' +@name_col_1 + ',  value,'

+ 'concat(''col'',ROW_NUMBER() over (PARTITION BY ' +@name_col_1 + ' order by ' +@name_col_1 +  ' )) AS LINHA ' +CHAR(13)

+ ' from ' + @table_name + CHAR(13)

+ 'cross apply STRING_SPLIT(' +@name_col_1 +','+''''+@delimited+''''+ ')  as t )z ' +CHAR(13)

+ 'pivot (max(value) FOR LINHA in ( ' +@col+' )) as pvt'

exec (@sql_batch)

Bem eu tinha uma string que era separada por  virgula, o que pensei? Preciso saber qual é a quantidade máxima de virgula que tenho em uma linha da tabela, porque isso seria a quantidade de colunas, foi ai que lembrei da dica do Fabiano que é genial.

Ele basicamente conta o tamanho total da linha, menos o tamanho total da linha menos o caractere procurado. A expressão seria assim (A) - (A-X) e no código ficaria assim:

select  LEN(line)-len(replace(line,',','')) from teste

Lembrando dessa dica apliquei o max nesse resultado, assim saberia a quantidade de colunas que precisava, algum assim:

select  max(LEN(line)-len(replace(line,',',''))) from teste

Agora que eu sei a quantidade máxima de colunas ficou fácil, eu monto uma CTE recursiva e aplico a função string_Agg para montar o script das colunas. Assim:

;with monta_col

as

(

select ID = 1

UNION ALL

SELECT ID+1 FROM  monta_col

WHERE ID < @qtd_col

)

SELECT  @col = STRING_AGG('[COL'+rtrim(ltrim(CAST(ID AS VARCHAR(50))))+']',',') FROM monta_col

OPTION(MAXRECURSION  0 )

Agora que tenho esse script eu utilizo a função STRING_SPLIT com row_number() para montar as colunas, assim:

set @sql_batch =  'select '+@name_col_1+',' + @col +CHAR(13)

+ 'from ('+CHAR(13)

+ 'select ' +@name_col_1 + ',  value,'

+ 'concat(''col'',ROW_NUMBER() over (PARTITION BY ' +@name_col_1 + ' order by ' +@name_col_1 +  ' )) AS LINHA ' +CHAR(13)

+ ' from ' + @table_name + CHAR(13)

+ 'cross apply STRING_SPLIT(' +@name_col_1 +','+''''+@delimited+''''+ ')  as t )z ' +CHAR(13)

+ 'pivot (max(value) FOR LINHA in ( ' +@col+' )) as pvt'

exec (@sql_batch)

Muito massa, né? O resulto ficou assim: