0

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?

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
Alessio Bacin
  • 71
  • 1
  • 1
  • 10
  • 4
    What's your DBMS? Anyway there are tons of answers about this. – dario Jan 28 '15 at 09:31
  • 3
    It very much depends on what database you are using. Please update your question with this vital information. – jpw Jan 28 '15 at 09:32
  • 1
    Another thing, which we greatly value before answering any questions is to show us what was your attempt at writing a query and find a solution yourself. – Radu Gheorghiu Jan 28 '15 at 09:35

2 Answers2

1

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
Disha
  • 392
  • 2
  • 7
0

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

select
  id,
  unnest(string_to_array(dvr,','))
from your_table;
UlfR
  • 4,175
  • 29
  • 45