Fala pessoal!!
Nesse artigo eu gostaria de trazer um cenário que de vez em quando me perguntam sobre como resolver, que é transformar strings de uma linha para colunas, muito utilizado para contas contábeis ou centro de custo, por exemplo, que foi exatamente a situação que me pediram ajuda há uns 10 dias e resolvi tirar um tempinho para escrever esse artigo.

Exemplo de tabela:

E aí a pessoa precisava quebrar os números dos centros de custos (separados por “.”) em colunas diferentes, para conseguir montar uma hierarquia dos centros de custos e realizar algumas análises/montar relatório em cima desses dados.

Solução #1 – STRING_SPLIT

Caso você esteja utilizando a versão 2016+ do SQL Server, pode utiliar a função STRING_SPLIT para atingir ao objetivo desejado:

DECLARE @Tabela TABLE ( Conta VARCHAR(100) )

INSERT INTO @Tabela 
VALUES 
    ('1'), ('01.04'), ('01.06'), ('01.04.14'), ('01.04.18'), 
    ('1.1.3'), ('1.5.6'), ('1.1.2'), ('1.4.3.1.4'), ('1.6.4.32')


SELECT *, ROW_NUMBER() OVER(PARTITION BY A.Conta ORDER BY A.Conta) AS Nivel
FROM @Tabela A
CROSS APPLY STRING_SPLIT(A.Conta, '.')

Resultado:

Uma das grandes reclamações dos usuários sobre a função STRING_SPLIT é a falta de um campo numérico e incremental retornando o índice de cada linha que foi quebrada. No exemplo acima, eu tive que criar essa linha utilizando uma função de ranking (row_number), mas fique atento que isso NÃO garante a ordenação correta.

Para converter essas várias linhas em colunas, podemos utilizar funções de agregação com CASE’s, supondo que o nível máximo seria de 10 níveis:

DECLARE @Tabela TABLE ( Conta VARCHAR(100) )

INSERT INTO @Tabela 
VALUES 
    ('1'), ('01.04'), ('01.06'), ('01.04.14'), ('01.04.18'), 
    ('1.1.3'), ('1.5.6'), ('1.1.2'), ('1.4.3.1.4'), ('1.6.4.32')


IF (OBJECT_ID('tempdb..#Tabela') IS NOT NULL) DROP TABLE #Tabela
;WITH dados AS (
    SELECT A.Conta, [value] AS Palavra, ROW_NUMBER() OVER(PARTITION BY A.Conta ORDER BY A.Conta) AS Nivel
    FROM @Tabela A
    CROSS APPLY STRING_SPLIT(A.Conta, '.')
)
SELECT 
    A.Conta, 
    MAX(CASE WHEN A.Nivel = 1 THEN CONVERT(INT, A.Palavra) END) AS Parte1,
    MAX(CASE WHEN A.Nivel = 2 THEN CONVERT(INT, A.Palavra) END) AS Parte2,
    MAX(CASE WHEN A.Nivel = 3 THEN CONVERT(INT, A.Palavra) END) AS Parte3,
    MAX(CASE WHEN A.Nivel = 4 THEN CONVERT(INT, A.Palavra) END) AS Parte4,
    MAX(CASE WHEN A.Nivel = 5 THEN CONVERT(INT, A.Palavra) END) AS Parte5,
    MAX(CASE WHEN A.Nivel = 6 THEN CONVERT(INT, A.Palavra) END) AS Parte6,
    MAX(CASE WHEN A.Nivel = 7 THEN CONVERT(INT, A.Palavra) END) AS Parte7,
    MAX(CASE WHEN A.Nivel = 8 THEN CONVERT(INT, A.Palavra) END) AS Parte8,
    MAX(CASE WHEN A.Nivel = 9 THEN CONVERT(INT, A.Palavra) END) AS Parte9,
    MAX(CASE WHEN A.Nivel = 10 THEN CONVERT(INT, A.Palavra) END) AS Parte10
FROM
    dados A
GROUP BY
    A.Conta

Resultado:

E caso você queira utilizar uma solução mais dinâmica, onde o número de níveis da tabela seja definido pelo número de níveis dos dados reais, pode utilizar também o código abaixo:

------------------------------------------------------
-- GERA OS DADOS
------------------------------------------------------

DECLARE @Tabela TABLE ( Conta VARCHAR(100) )

INSERT INTO @Tabela 
VALUES 
    ('1'), ('01.04'), ('01.06'), ('01.04.14'), ('01.04.18'), 
    ('1.1.3'), ('1.5.6'), ('1.1.2'), ('1.4.3.1.4'), ('1.6.4.32')


------------------------------------------------------
-- QUEBRA AS CONTAS EM PARTES
------------------------------------------------------

IF (OBJECT_ID('tempdb..#Partes') IS NOT NULL) DROP TABLE #Partes
SELECT 
    A.Conta, 
    CONVERT(INT, [value]) AS Parte, 
    ROW_NUMBER() OVER(PARTITION BY A.Conta ORDER BY A.Conta) AS Nivel
INTO
    #Partes
FROM
    @Tabela A
    CROSS APPLY STRING_SPLIT(A.Conta, '.')


------------------------------------------------------
-- GERA OS SCRIPTS PRA CRIAÇÃO E ATUALIZAÇÃO DINÂMICA
------------------------------------------------------

DECLARE 
    @Contador INT = 1, 
    @Total INT = (SELECT MAX(Nivel) FROM #Partes),
    @QueryCreate VARCHAR(MAX) = 'CREATE TABLE ##Tabela ( Conta VARCHAR(100)',
    @QueryUpdate VARCHAR(MAX) = ''


WHILE(@Contador <= @Total)
BEGIN
    SET @QueryCreate += ', Nivel' + CONVERT(VARCHAR(10), @Contador) + ' INT'
    SET @QueryUpdate += 'UPDATE A SET A.Nivel' + CONVERT(VARCHAR(10), @Contador) + ' = B.Parte FROM ##Tabela A JOIN #Partes B ON A.Conta = B.Conta WHERE B.Nivel = ' + CONVERT(VARCHAR(10), @Contador) + '; '
    SET @Contador += 1
END

SET @QueryCreate += ' )'


------------------------------------------------------
-- CRIA A TABELA DINAMICAMENTE
------------------------------------------------------

IF (OBJECT_ID('tempdb..##Tabela') IS NOT NULL) DROP TABLE ##Tabela
EXEC(@QueryCreate)

INSERT INTO ##Tabela ( Conta )
SELECT DISTINCT Conta
FROM #Partes


------------------------------------------------------
-- ATUALIZA OS DADOS DINAMICAMENTE
------------------------------------------------------

EXEC(@QueryUpdate)


------------------------------------------------------
-- RESULTADO FINAL
------------------------------------------------------

SELECT * FROM ##Tabela

Resultado:

Solução #2 – fncSplitTexto

Caso você esteja utilizando uma versão anterior à 2016 do SQL Server, a função STRING_SPLIT não poderá ser utilizada. Entretanto, você pode muito bem utilizar a função fncSplitTexto, que disponibilizei no artigo Como quebrar um string em uma tabela de substrings utilizando um delimitador no SQL Server.

A vantagem do uso dessa função, além da retrocompatibilidade, é que ela já possui o indexador por padrão.

Uma vez criada, a sua utilização é quase idêntica ao uso da STRING_SPLIT:

DECLARE @Tabela TABLE ( Conta VARCHAR(100) )

INSERT INTO @Tabela 
VALUES 
    ('1'), ('01.04'), ('01.06'), ('01.04.14'), ('01.04.18'), 
    ('1.1.3'), ('1.5.6'), ('1.1.2'), ('1.4.3.1.4'), ('1.6.4.32')


IF (OBJECT_ID('tempdb..#Tabela') IS NOT NULL) DROP TABLE #Tabela
SELECT 
    Conta, 
    MAX(CASE WHEN B.Id = 1 THEN CONVERT(INT, B.Palavra) END) AS Parte1,
    MAX(CASE WHEN B.Id = 2 THEN CONVERT(INT, B.Palavra) END) AS Parte2,
    MAX(CASE WHEN B.Id = 3 THEN CONVERT(INT, B.Palavra) END) AS Parte3,
    MAX(CASE WHEN B.Id = 4 THEN CONVERT(INT, B.Palavra) END) AS Parte4,
    MAX(CASE WHEN B.Id = 5 THEN CONVERT(INT, B.Palavra) END) AS Parte5,
    MAX(CASE WHEN B.Id = 6 THEN CONVERT(INT, B.Palavra) END) AS Parte6,
    MAX(CASE WHEN B.Id = 7 THEN CONVERT(INT, B.Palavra) END) AS Parte7,
    MAX(CASE WHEN B.Id = 8 THEN CONVERT(INT, B.Palavra) END) AS Parte8,
    MAX(CASE WHEN B.Id = 9 THEN CONVERT(INT, B.Palavra) END) AS Parte9,
    MAX(CASE WHEN B.Id = 10 THEN CONVERT(INT, B.Palavra) END) AS Parte10
FROM
    @Tabela 
    CROSS APPLY dbo.fncSplitTexto(Conta, '.') AS B
GROUP BY
    Conta

Resultado:

E para ler os dados dinamicamente, de acordo com as colunas existentes nos dados, você pode utilizar o código abaixo:

------------------------------------------------------
-- GERA OS DADOS
------------------------------------------------------

DECLARE @Tabela TABLE ( Conta VARCHAR(100) )

INSERT INTO @Tabela 
VALUES 
    ('1'), ('01.04'), ('01.06'), ('01.04.14'), ('01.04.18'), 
    ('1.1.3'), ('1.5.6'), ('1.1.2'), ('1.4.3.1.4'), ('1.6.4.32')


------------------------------------------------------
-- QUEBRA AS CONTAS EM PARTES
------------------------------------------------------

IF (OBJECT_ID('tempdb..#Partes') IS NOT NULL) DROP TABLE #Partes
SELECT 
    Conta, 
    B.Id, 
    CONVERT(INT, B.Palavra) AS Parte
INTO
    #Partes
FROM
    @Tabela 
    CROSS APPLY dbo.fncSplitTexto(Conta, '.') AS B


------------------------------------------------------
-- GERA OS SCRIPTS PRA CRIAÇÃO E ATUALIZAÇÃO DINÂMICA
------------------------------------------------------

DECLARE 
    @Contador INT = 1, 
    @Total INT = (SELECT MAX(Id) FROM #Partes),
    @QueryCreate VARCHAR(MAX) = 'CREATE TABLE ##Tabela ( Conta VARCHAR(100)',
    @QueryUpdate VARCHAR(MAX) = ''


WHILE(@Contador <= @Total)
BEGIN
    SET @QueryCreate += ', Parte' + CONVERT(VARCHAR(10), @Contador) + ' INT'
    SET @QueryUpdate += 'UPDATE A SET A.Parte' + CONVERT(VARCHAR(10), @Contador) + ' = B.Parte FROM ##Tabela A JOIN #Partes B ON A.Conta = B.Conta WHERE B.Id = ' + CONVERT(VARCHAR(10), @Contador) + '; '
    SET @Contador += 1
END

SET @QueryCreate += ' )'


------------------------------------------------------
-- CRIA A TABELA DINAMICAMENTE
------------------------------------------------------

IF (OBJECT_ID('tempdb..##Tabela') IS NOT NULL) DROP TABLE ##Tabela
EXEC(@QueryCreate)

INSERT INTO ##Tabela ( Conta )
SELECT DISTINCT Conta
FROM #Partes


------------------------------------------------------
-- ATUALIZA OS DADOS DINAMICAMENTE
------------------------------------------------------

EXEC(@QueryUpdate)


------------------------------------------------------
-- RESULTADO FINAL
------------------------------------------------------

SELECT * FROM ##Tabela

Resultado:

A performance das duas funções

Se você já estudou um pouco a parte de Performance Tuning ou já leu o meu artigo SQL Server – Comparação de performance entre Scalar Function e CLR Scalar Function, tenho certeza que você já sabe que a utilização da STRING_SPLIT é bem mais rápido que utilizar uma UDF (User Defined Function), mesmo que TVF (Table-valued Function).

Mesmo assim, vou mostrar a diferença de performance da função nativa (vou testar com e sem o ROW_NUMBER) com uma função UDF, num volume de dados de aproximadamente 20 mil registros:

Como vocês puderam ver, a diferença de performance é gigantesca entre uma UDF e uma função nativa, e quanto mais dados na tabela, maior é a diferença. Por este motivo, recomendo utilizar sempre a função nativa STRING_SPLIT, quando possível.

Bom pessoal, espero que vocês tenham gostado desse artigo.
Um grande abraço e até o próximo!