¡¡Hola, chicos!!
En este artículo me gustaría mencionar un escenario que ocasionalmente me preguntan cómo resolver, que es transformar cadenas de una línea a columnas, que se usa a menudo para cuentas contables o centros de costos, por ejemplo, que fue exactamente la situación con la que me pidieron ayuda hace unos 10 días y decidí tomarme un tiempo para escribir este artículo.

Ejemplo de tabla:

Y luego la persona necesitaba dividir los números de los centros de costos (separados por “.”) en diferentes columnas, para poder crear una jerarquía de centros de costos y realizar algunos análisis/elaborar informes basados ​​en estos datos.

Solución n.º 1: STRING_SPLIT

Si está utilizando la versión 2016+ de SQL Server, puede usar la función STRING_SPLIT para lograr el objetivo deseado:

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:

Una de las mayores quejas de los usuarios sobre la función STRING_SPLIT es la falta de un campo numérico e incremental que devuelva el índice de cada línea que se rompió. En el ejemplo anterior, tuve que crear esta fila usando una función de clasificación (número_fila), pero tenga en cuenta que esto NO garantiza el pedido correcto.

Para convertir estas múltiples filas en columnas, podemos usar funciones de agregación con CASE, asumiendo que el nivel máximo sería 10 niveles:

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:

Y si desea utilizar una solución más dinámica, donde la cantidad de niveles en la tabla se define por la cantidad de niveles en los datos reales, también puede usar el siguiente código:

------------------------------------------------------
-- 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:

Solución #2 – fncSplitTexto

Si está utilizando una versión de SQL Server anterior a 2016, la función STRING_SPLIT no se puede utilizar. Sin embargo, puedes utilizar la función fncSplitTexto, que puse a disposición en el artículo. Cómo dividir una cadena en una tabla de subcadenas usando un delimitador en SQL Server.

La ventaja de utilizar esta función, además de la compatibilidad con versiones anteriores, es que ya cuenta con el indexador por defecto.

Una vez creado, su uso es casi idéntico al uso de 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:

Y para leer los datos dinámicamente, de acuerdo con las columnas existentes en los datos, puede usar el siguiente código:

------------------------------------------------------
-- 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:

El desempeño de las dos funciones.

Si ya has estudiado un poco Performance Tuning o has leído mi artículo SQL Server: comparación de rendimiento entre la función escalar y la función escalar CLR, Estoy seguro de que ya sabes que usar STRING_SPLIT es mucho más rápido que usar una UDF (función definida por el usuario), incluso una TVF (función con valores de tabla).

Aun así, mostraré la diferencia de rendimiento entre la función nativa (probaré con y sin ROW_NUMBER) con una función UDF, sobre un volumen de datos de aproximadamente 20 mil registros:

Como puede ver, la diferencia de rendimiento es enorme entre una UDF y una función nativa, y cuantos más datos haya en la tabla, mayor será la diferencia. Por este motivo, recomiendo utilizar siempre la función nativa STRING_SPLIT, cuando sea posible.

Bueno chicos, espero que les haya gustado este artículo.
¡Un abrazo grande y hasta la próxima!