How to Split a Column's Data into Multiple Rows in SQL?

How to Split Column Data into Rows in SQL?

When working with relational databases, you may encounter situations where you need to split the data in a single column into multiple rows. This technique is particularly useful for scenarios such as separating a comma-separated list of items into individual rows.

To achieve this, you can leverage the power of string manipulation functions in SQL. Let's explore two methods to split column data into rows:

1. Using a Split Function:

You can create a custom split function to divide the column data based on a specific delimiter, such as a comma or semicolon. Here's a sample function you can use:

CREATE FUNCTION [dbo].[Split](@String VARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @temptable TABLE (items VARCHAR(MAX))
AS
BEGIN
    DECLARE @idx INT
    DECLARE @slice VARCHAR(8000)

    SELECT @idx = 1
    IF LEN(@String)<1 OR @String IS NULL RETURN

    WHILE @idx!= 0
    BEGIN
        SET @idx = CHARINDEX(@Delimiter,@String)
        IF @idx!=0
            SET @slice = LEFT(@String,@idx - 1)
        ELSE
            SET @slice = @String

        IF(LEN(@slice)>0)
            INSERT INTO @temptable(Items) VALUES(@slice)

        SET @String = RIGHT(@String,LEN(@String) - @idx)
        IF LEN(@String) = 0 BREAK
    END

    RETURN
END;

2. Using a Recursive CTE:

Alternatively, you can use a recursive Common Table Expression (CTE) to achieve the same result. Here's a CTE solution:

WITH cte (code, DeclarationItem, Declaration) AS
(
  SELECT Code,
    CAST(LEFT(Declaration, CHARINDEX(',',Declaration+',')-1) AS VARCHAR(50)) DeclarationItem,
         STUFF(Declaration, 1, CHARINDEX(',',Declaration+','), '') Declaration
  FROM yourtable
  UNION ALL
  SELECT code,
    CAST(LEFT(Declaration, CHARINDEX(',',Declaration+',')-1) AS VARCHAR(50)) DeclarationItem,
    STUFF(Declaration, 1, CHARINDEX(',',Declaration+','), '') Declaration
  FROM cte
  WHERE Declaration > ''
)
SELECT code, DeclarationItem
FROM cte

Example Usage:

To split the column 'Declaration' in the 'yourtable' table into individual rows, you can use either of the above methods. For instance:

SELECT t1.code, s.items AS declaration
FROM yourtable t1
OUTER APPLY dbo.split(t1.declaration, ',') s

This query will produce a result where the 'Declaration' column is split into separate rows for each item.

The above is the detailed content of How to Split a Column's Data into Multiple Rows in SQL?. For more information, please follow other related articles on the PHP Chinese website!