CODEWITHSUNDEEP
sqlxmlsql-server-2008loops
ifallelsefailthenstackoverflow
ifallelsefailthenstackoverflow

Reputation: 135

loop a variable delimited by comma and enter each item to each row of the table SQL

lets say I have the variable x, which is equal to: x='3,4,5,6,7'

Then i have a table @tmpTable with two columns (respID and Responses)

On my @tmpTable the respIDs for each row are null.

I want the ids of each row there to be the values on my x variable above. (for example, row 1's respID=1, row 2's respID=2.. and so on..)

enter image description here how to do this in SQL?

Upvotes: 0

Views: 62

Answers (2)

binarymnl
binarymnl

Reputation: 153

You can achieve as below using SSMS:

declare @S varchar(20)
set @S = '1,2,3,4,5'
declare @tempTable as table (col1 varchar(max), col2 varchar(max))

While len(@s) > 0
begin
    insert into @tempTable(col1) select left(@S, charindex(',', @S+',')-1)
    set @S=stuff(@S, 1, charindex(',', @S+','), '')
end

select * from @tempTable

Upvotes: 1

Andrew
Andrew

Reputation: 5277

You can do something like this.

SELECT 
  Responses.value('(/x/@ID)[1]', 'int') AS [ID],
  Responses
FROM YourTable

Sorry the image you had in your post has now disappeared so I don't remember the table name or the exact xml. Have a search on google for "tsql xml xpath".

Upvotes: 0

Related Questions