Início / dba / Perguntas / 273638
Accepted
Mike S
Asked: 2020-08-13 05:31:17 +0800 CST

Como você pode dividir várias strings delimitadas de tamanhos variados em linhas?

  • 772

Eu tenho uma situação em que, para cada ID na minha tabela, existem vários campos, cada um contendo uma string delimitada por vírgulas. As strings não têm necessariamente o mesmo número de elementos umas das outras. O formato dos dados vem de outra equipe e não posso alterá-lo.

Como posso dividir essas strings em linhas com o SQL Server 2016? Prefiro não adicionar uma função ao banco de dados se isso puder ser evitado.

Aqui está uma amostra do que estou começando e o que estou procurando alcançar:

DECLARE @SourceTable TABLE (ID INT, String1 VARCHAR(100), 
  String2 VARCHAR(100), String3 VARCHAR(100))

INSERT INTO @SourceTable (ID, String1, String2, String3)
VALUES (1, 'a1,a2,a3', 'b1,b2,b3,b4,b5,b6', 'c1,c2'),
       (2, 'd1,d2', 'e1', 'f1,f2')

--Source
SELECT *
FROM @SourceTable

Saída Desejada:

ID  Position    S1_Transformed  S2_Transformed  S3_Transformed
--  --------    --------------  --------------  --------------
1   1           a1              b1              c1
1   2           a2              b2              c2
1   3           a3              b3  
1   4                           b4  
1   5                           b5  
1   6                           b6  
2   1           d1              e1              f1
2   2           d2                              f2

EDIT: saída corrigida com base no comentário de Aaron.

sql-server sql-server-2016

1 respostas

  1. Best Answer

    Se você pode suportar ter uma função para fornecer funcionalidades não (ainda) incorporadas ao mecanismo, você pode criar esta função que retorna uma posição ordinal ao lado de cada valor (há dezenas de maneiras diferentes de escrever essa função de divisão):

    CREATE FUNCTION dbo.SplitCSVInOrder
(
    @List  nvarchar(max)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN
    (
      WITH L0(c) AS (SELECT 1 UNION ALL SELECT 1),
           L1(c) AS (SELECT 1 FROM L0, L0 AS B),
           L2(c) AS (SELECT 1 FROM L1, L1 AS B),
           L3(c) AS (SELECT 1 FROM L2, L2 AS B),
           L4(c) AS (SELECT 1 FROM L3, L3 AS B),
           L5(c) AS (SELECT 1 FROM L4, L4 AS B),
           N (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY c) FROM L5)
        SELECT 
           [Position] = ROW_NUMBER() OVER (ORDER BY n),
           [Value] = LTRIM(RTRIM(SUBSTRING(@List, n,
                CHARINDEX(',', @List + ',', n) - n)))
        FROM N WHERE n <= LEN(@List)
            AND SUBSTRING(',' + @List, n, 1) = ','
    );

    Então você pode unpivot/re-pivot para alinhar os valores de cada posição:

    DECLARE @SourceTable TABLE
( 
  ID INT, 
  String1 varchar(100), 
  String2 varchar(100), 
  String3 varchar(100)
);

INSERT INTO @SourceTable (ID, String1, String2, String3)
VALUES (1, 'a1,a2,a3', 'b1,b2,b3,b4,b5,b6', 'c1,c2'),
       (2, 'd1,d2', 'e1', 'f1,f2');

;WITH x AS 
(
  SELECT * FROM @SourceTable
    UNPIVOT (s FOR strings IN (String1, String2, String3)) AS up
    CROSS APPLY dbo.SplitCSVInOrder(s)
),
p AS 
(
  SELECT * FROM x PIVOT (MAX(Value) 
    FOR strings IN ([String1],[String2],[String3])) AS p
)
SELECT ID, Position, S1_Transformed = COALESCE(MIN(String1),''), 
                     S2_Transformed = COALESCE(MIN(String2),''), 
                     S3_Transformed = COALESCE(MIN(String3),'')
FROM p
GROUP BY ID, Position
ORDER BY ID, Position;

    Resultados:

    ID  Position    S1_Transformed  S2_Transformed  S3_Transformed
--  --------    --------------  --------------  --------------
1   1           a1              b1              c1
1   2           a2              b2              c2
1   3           a3              b3  
1   4                           b4  
1   5                           b5  
1   6                           b6  
2   1           d1              e1              f1
2   2           d2                              f2

    Tenho certeza de que há uma maneira mais simples de fazer isso, mas foi isso que meu cérebro criado principalmente no SQL Server-2005 criou.

    Você gostaria que essa consulta pudesse ser mais fácil de formular? Eu também! Por um lado, seria ótimo se houvesse uma função de divisão de string nativa que também retornasse a posição ordinal. Isso negaria a necessidade de uma função para garantir que a saída saia na mesma ordem em que está listada na string. Pedi a ajuda de todos para que a Microsoft adicionasse uma função na próxima versão do SQL Server. Por favor, vote e adicione um comentário construtivo que explique seu caso de negócios aqui:

    E veja alguns antecedentes sobre o que estou pedindo especificamente aqui:

    • 1

relate perguntas