August 24, 2004 at 2:07 pm
I have a question. How I can convert a column resultset to rows?
e.g.
Column Resultset
create proc usp_siteMng (@idt varchar(200)) -- parameter: 123,345,567,789
as
declare @cmd nvarchar(100)
set @cmd = 'SELECT ' + @idt
create table #tmpSite (idtSite int primary key)
insert into #tmpSite
exec sp_executesql @cmd
...
This returns an error because the resultset is just in one row.
How can I convert it in many rows?
August 24, 2004 at 2:16 pm
If the above is really your setup then a simple while loop is all you are after. We created the following function to do exactly that. Given a comma delimeted string it returns a table of its values. If its more complicated than that I'll need a little more information to offer any real advice.
CREATE FUNCTION Split (@List varchar(1000)) RETURNS @Results table (Item varchar(1000)) AS begin declare @IndexStart int declare @IndexEnd int declare @Length int declare @Delim char(1) declare @Word varchar(1000) declare @Kill int set @IndexStart = 1 set @IndexEnd = 0 set @Length = len(@List) set @Delim = ',' set @Kill = 0
while @IndexStart <= @Length begin set @Kill = @Kill + 1 if @Kill >= 999 return -- hard limiter just in case
set @IndexEnd = charindex(@Delim, @List, @IndexStart)
if @IndexEnd = 0 set @IndexEnd = @Length + 1
set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)
set @IndexStart = @IndexEnd + 1
INSERT INTO @Results SELECT @Word end
return end |
August 24, 2004 at 10:23 pm
Can you please explain me the scenario where this is exactly required?
August 25, 2004 at 7:00 am
It seems that Aaron's answer might be what you are looking for, to turn a list of comma separated values into a table of ID values which can then be joined with another table to get text values. See the website referenced in the function for more information and explanations. I also pasted a use of the function where it is inner joined with the Group_Type table to return the comma separated group type text descriptions from a list of the ID values.
CREATE FUNCTION dbo.iter_intlist_to_table (@list ntext)
--from http://www.sommarskog.se/arrays-in-sql.html#iterative
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@STR nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
SET @textpos = @textpos + @chunklen
SET @pos = charindex(',', @tmpstr)
WHILE @pos > 0
BEGIN
SET @STR = substring(@tmpstr, 1, @pos - 1)
INSERT @tbl (number) VALUES(convert(int, @STR))
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(',', @tmpstr)
END
SET @leftover = @tmpstr
END
IF ltrim(rtrim(@leftover)) <> ''
INSERT @tbl (number) VALUES(convert(int, @leftover))
RETURN
END
-------------USE-------------
CREATE FUNCTION dbo.Group_Type_List(@Group_Type_IDs nvarchar(3000))
RETURNS NVARCHAR(3000)
AS
BEGIN
DECLARE @Group_Type_List NVARCHAR(3000)
SELECT @Group_Type_List = ISNULL(@Group_Type_List + ', ', '')
+ Group_Type
FROM iter_intlist_to_table (@Group_Type_IDs) Group_Types
INNER JOIN Group_Type ON
Group_Types.number = Group_Type.Group_Type_ID
RETURN(@Group_Type_List)
END
August 25, 2004 at 1:39 pm
Thansk Aaron and Willian
Its just what I looking for. I will test the both. We are right, I need a way of turn a list of values into a table of IDs. I know that is not possible with a single T-SQL statement then I looking for a function witch do this.
Regards,
Luciano
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply