Splitting and transposing table data using SQL Server

Question

I have data in the following structure

 ID   Sub_ID
 123  '1;2;3'

I would like to create a table with the following structure

ID Sub_ID
123 1
123 2
123 3

Is there any way to process this task on a Microsoft SQL-Server?

THX Lazloo

Answer 1

If you are using SQL Server 2016 or later, then STRING_SPLIT is one option here:

WITH cte AS (
    SELECT ID, value
    FROM yourTable
        CROSS APPLY STRING_SPLIT(Sub_ID, ';')
)

SELECT *
FROM cte
ORDER BY value;

Demo

If you are using an earlier version of SQL Server, then here is a link to a canonical SO question which can help. If you have this need long term, maybe consider upgrading.

Answer 2

If you older version then SQL Server 2016 you need to create your own split function:

CREATE  FUNCTION [dbo].[Tbl_Fn_Split](
    @InputText VARCHAR(8000) 
  , @Delimiter VARCHAR(8000) = ' ' -- delimiter that separates items
) RETURNS @List TABLE (Result VARCHAR(8000))

BEGIN
DECLARE @aResult VARCHAR(8000)
WHILE CHARINDEX(@Delimiter,@InputText,0) <> 0
BEGIN
    SELECT
        @aResult=RTRIM(LTRIM(SUBSTRING(@InputText,1,CHARINDEX(@Delimiter,@InputText,0)-1))),
        @InputText=RTRIM(LTRIM(SUBSTRING(@InputText,CHARINDEX(@Delimiter,@InputText,0)+LEN(@Delimiter),LEN(@InputText))))

    IF LEN(@aResult) > 0
        INSERT INTO @List SELECT @aResult
    END

    IF LEN(@InputText) > 0
        INSERT INTO @List SELECT @InputText 

    RETURN

END

Then you can use it with your query using CROSS APPLY :

SELECT * FROM tTable  T
CROSS APPLY (SELECT * FROM [dbo].[Tbl_Fn_Split](T.Sub_Id,';')) S