June 6, 2003 at 2:36 pm
Ok, what I need to try to do is this. I want to be able to Select a comma delimited list of Values from one table, where they match the ID of a Parent Table, and have those Values in 1 (one) column in the returned Row(s).
For example:
Employees
EmployeeChildren (Tables can be Joined by EmpID)
Ok, what the Resultset would look like would be something like this:
FirstName, LastName, [ChildrenList]
I have seen how do to something similar using a "Case" statement, but that will not work for this because there is no pre-defined set number of Values coming from the Sub-Table.
Any ideas?
June 6, 2003 at 3:05 pm
This example uses pubs, but the idea's the same...
CREATE FUNCTION BookList
(@au_id id)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Booklist VARCHAR(8000)
SELECT @Booklist = ISNULL(@Booklist + ',' + t.title, t.title) from
titles t, titleauthor ta
where ta.au_id = @au_id
and t.title_id = ta.title_id
RETURN @Booklist
END
GO
SELECT au_lname, au_fname, Booklist = dbo.BookList (au_id) from authors
GO
Cheers,
- Mark
June 6, 2003 at 8:23 pm
If you are trying to do this in QA and not from another app you can save a bit of coding effort if you change the results output format under Tools->Options... -> "Results" tab and you will find it. Just FYI.
June 9, 2003 at 6:41 am
Sweet! Thanks, this worked! One more question...is it possible to make a Generic function for any Table that I might want to do this to?
Example:
CREATE FUNCTION GetDataList
(@TableName VarChar(128), @KeyColumn VarChar(25), @ListColumn VarChar(25), @ID SmallInt)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @List VARCHAR(8000)
Select @List = IsNull(@List + ',' + @ListColumn, @ListColumn)
From @Table
Where @KeyColumn = @ID
RETURN @List
END
GO
I know the above Syntax is totally wrong, but is there someway possible? If not, I guess I'll deal with it =P.
Thanks for the help!
June 9, 2003 at 7:58 am
Not sure if it will work. Also if you don't specify the table name etc. the optimiser will have trouble compiling the function and you may not get many speed benefits.
You could use sysobjects to build a more generic function.
Besides, do you really need a delimited list of every table?
June 9, 2003 at 8:00 am
Probably not, I just wondered if it could be done. Most of the time, if possible, I like to create Generic routines...if it can't be done, so be it =0).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply