CODEWITHSUNDEEP
sql-server-2008t-sqlcsv
Murtaza Mandvi
Murtaza Mandvi

Reputation: 10998

Convert csv values to table

DATA:

id  data
114 10047,10001,10003
123 90080
233 10020,10029,10032,10065

TABLE:

DECLARE @TEMP TABLE (id int 
                    ,data varchar(50)
                    PRIMARY KEY (id)
                    )

INSERT INTO @TEMP VALUES (114,'10047,10001,10003')
INSERT INTO @TEMP VALUES (123,'90080')
INSERT INTO @TEMP VALUES (233,'10020,10029,10032,10065')

Given the above data I am trying to convert the comma separated values to a table so I can join the values, I already have a function that works fine by taking a single value as a parameter, how do I call the function so that it goes through each of the rows above (without using cursors) and outputs the values as such :

id  data
114 10047
114 10001
114 10003
123 90080
233 10020
233 10029
233 10032
233 10065

Function:

FUNCTION [dbo].[ConvertCsvToTable](@csvList nvarchar(MAX))
RETURNS @csvTable table([id] int NOT NULL)

AS
BEGIN
DECLARE @pos int
DECLARE @nextPos int
DECLARE @valueLen int

SELECT @pos = 0, @nextPos = 1

WHILE @nextPos > 0
BEGIN
  SELECT @nextPos = charindex(',', @csvList, @pos + 1)
  SELECT @valueLen = CASE WHEN @nextPos > 0
                          THEN @nextPos
                          ELSE len(@csvList) + 1
                     END - @pos - 1
  INSERT @csvTable (id)
     VALUES (convert(int, substring(@csvList, @pos + 1, @valueLen)))
  SELECT @pos = @nextPos
END

RETURN
END

Upvotes: 0

Views: 1572

Answers (1)

Serg
Serg

Reputation: 2427

You can use simplier solution if you convert your comma separated values to XML data. Your result could be achieved with a single select statement like as follows:

-- Improve table structure adding XML field
DECLARE @TEMP TABLE (id int 
                ,data varchar(50)
                ,xmldata xml
                PRIMARY KEY (id)
                )

-- Your initial data
INSERT INTO @TEMP VALUES (114,'10047,10001,10003',null)
INSERT INTO @TEMP VALUES (123,'90080',null)
INSERT INTO @TEMP VALUES (233,'10020,10029,10032,10065',null)

-- Transforming CSV string to XML
UPDATE @TEMP
SET xmldata = CONVERT(XML, '<DATA><RAW>'+REPLACE(data, ',', '</RAW><RAW>')+'</RAW></DATA>')

-- Query XML data to obtain required result
SELECT  t.id,
        x.xmldata.value('(.)[1]','INT') AS data
  FROM  @TEMP t
  CROSS APPLY t.xmldata.nodes('/DATA/RAW') x(xmldata);

Upvotes: 1

Related Questions