June 3, 2011 at 10:01 am
Selecting Recursively
I have a table which has records in the format
ID DEsc Problem
1 this is a problem 123
2 this is the same problem 123
3 this the same problem as well 123
All the descriptions are different but belong to the same problem
I want to combine the values and select them into a single statement
I am using
Select Desc from table X
Where ID between 1 and 100
I want to find a more efficient way if there is one to do this so that I can select more than 100 if such a situation arises, and combine all the desc of a particular problem into one while selecting
any hints or help is greatly appreciated
June 3, 2011 at 11:07 am
something like this should you what you are looking for. Notice how i created some ddl and sample data to make this easy for others to test. To paraphrase Wayne, do not use this code in a production environment unless you actually read it and understand what it does. After all at 3am it will be YOU that has to support it. Also you should avoid column names that are reserved words in sql (desc).
create table #recurse
(
id int identity(1,1),
val varchar(50),
problemID int
)
go
insert #recurse
select 'this is a problem', 123
union all
select 'this is the same problem', 123
union all
select 'this is the same problem as well', 123
SELECT LEFT(vals.dsc,LEN(vals.dsc)-1)
FROM
(
select val + ', '
from #recurse
for xml path('')
)vals(dsc)
drop table #recurse
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply