Convertir una cadena separada por comas en filas individuales

Tengo una tabla SQL como esta:

Alguna ID	Otro ID	Datos
a B C D e F-.....	cdef123-...	18,20,22
a B C D e F-.....	4554a24-...	17,19
987654-.....	12324a2-...	13,19,20

¿Existe alguna consulta en la que pueda realizar una consulta SELECT OtherID, SplitData WHERE SomeID = 'abcdef-.......'que devuelva filas individuales, como esta:

Otro ID	Dividir datos
cdef123-...	18
cdef123-...	20
cdef123-...	22
4554a24-...	17
4554a24-...	19

¿Básicamente dividir mis datos en la coma en filas individuales?

Soy consciente de que almacenar uncomma-separated cadena en una base de datos relacional suena tonto, pero el caso de uso normal en la aplicación de consumo lo hace realmente útil.

No quiero dividir la aplicación porque necesito paginación, así que quería explorar opciones antes de refactorizar toda la aplicación.

Es SQL Server 2008(no R2).

Mar 31 '11 06:03 Michael Stum

Puede utilizar las maravillosas funciones recursivas de SQL Server:

Tabla de muestra:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

La consulta

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

Producción

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4

Mar 30 '2011 23:03 RichardTheKiwi

Finalmente, la espera terminó con SQL Server 2016 . Han introducido la función Dividir cadena STRING_SPLIT:

select OtherID, cs.Value --SplitData
from yourtable
cross apply STRING_SPLIT (Data, ',') cs

Todos los demás métodos para dividir cadenas como XML, tabla Tally, bucle while, etc. han quedado impresionados con esta STRING_SPLITfunción.

Aquí hay un excelente artículo con comparación de rendimiento: Suposiciones y sorpresas de rendimiento: STRING_SPLIT .

Para versiones anteriores, el uso de la tabla de conteo aquí es una función de cadena dividida (el mejor enfoque posible)

CREATE FUNCTION [dbo].[DelimitedSplit8K]
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
     -- enough to cover NVARCHAR(4000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

Referido desde Tally OH! Una función mejorada de “divisor CSV” de SQL 8K

Mar 30 '2016 10:03 Pரதீப்

Mira esto

 SELECT A.OtherID,  
     Split.a.value('.', 'VARCHAR(100)') AS Data  
 FROM  
 (
     SELECT OtherID,  
         CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  Table1
 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a);

Apr 18 '2013 12:04 bvr

Muy tarde pero prueba esto:

SELECT ColumnID, Column1, value  --Do not change 'value' name. Leave it as it is.
FROM tbl_Sample  
CROSS APPLY STRING_SPLIT(Tags, ','); --'Tags' is the name of column containing comma separated values

Entonces teníamos esto: tbl_Sample :

ColumnID|   Column1 |   Tags
--------|-----------|-------------
1       |   ABC     |   10,11,12    
2       |   PQR     |   20,21,22

Después de ejecutar esta consulta:

ColumnID|   Column1 |   value
--------|-----------|-----------
1       |   ABC     |   10
1       |   ABC     |   11
1       |   ABC     |   12
2       |   PQR     |   20
2       |   PQR     |   21
2       |   PQR     |   22

¡Gracias!

Jan 07 '2019 06:01 Dungeon

select t.OtherID,x.Kod
    from testData t
    cross apply (select Code from dbo.Split(t.Data,',') ) x

Jan 28 '2014 15:01 tomlost