CODEWITHSUNDEEP
sqlsplit
Alessio Bacin
Alessio Bacin

Reputation: 69

Query to split String value into multiple rows

i have 

id | dvr
1  | 1,2,3
2  | 1,3,4
3  | 1,5,6,7,8

and would like to have

id | dvr
1  | 1
1  | 2
1  | 3
2  | 1
2  | 3
2  | 4
... and so on

what is the fastest query i should use?

Upvotes: 0

Views: 956

Answers (2)

Disha
Disha

Reputation: 392

Make a sql function as below:

create Function [dbo].[fun_CSVToTable] 
(
    @LIST varchar(7000),
    @Delimeter varchar(10)
)
RETURNS @RET1 TABLE (RESULT BIGINT)
AS
BEGIN
    DECLARE @RET TABLE(RESULT BIGINT)

    IF LTRIM(RTRIM(@LIST))='' RETURN  

    DECLARE @START BIGINT
    DECLARE @LASTSTART BIGINT
    SET @LASTSTART=0
    SET @START=CHARINDEX(@Delimeter,@LIST,0)

    IF @START=0
    INSERT INTO @RET VALUES(SUBSTRING(@LIST,0,LEN(@LIST)+1))

    WHILE(@START >0)
    BEGIN
        INSERT INTO @RET VALUES(SUBSTRING(@LIST,@LASTSTART,@START-@LASTSTART))
        SET @LASTSTART=@START+1
        SET @START=CHARINDEX(@Delimeter,@LIST,@START+1)
        IF(@START=0)
        INSERT INTO @RET VALUES(SUBSTRING(@LIST,@LASTSTART,LEN(@LIST)+1))
    END

    INSERT INTO @RET1 SELECT * FROM @RET
    RETURN 
END

Upvotes: 1

UlfR
UlfR

Reputation: 4395

If you are running postgresql and dvr column is text you could do:

select
  id,
  unnest(string_to_array(dvr,','))
from your_table;

Upvotes: 0

Related Questions