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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy