RE: Select statement
From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 10/26/04
- Next message: Rizwan: "Re: Need an option setting"
- Previous message: David Portas: "RE: Select statement"
- In reply to: Steven Richardson: "Select statement"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 26 Oct 2004 09:43:04 -0700
You can create a user function that return the list. The main cons is that
the longest value is 8000 characters.
Example:
use pubs
go
create function dbo.ufn_authors_list (
@title_id [tid]
)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s = ''
select
@s = @s + case when @s > '' then ',' else '' end + au_lname + ' ' +
au_fname
from
dbo.titleauthor as ta
inner join
dbo.authors as a
on a.au_id = ta.au_id and ta.title_id = @title_id
return nullif(@s, '')
end
go
select
t.title_id,
t.title,
dbo.ufn_authors_list(t.title_id) as authors_list
from
dbo.titles as t
go
drop function dbo.ufn_authors_list
go
AMB
"Steven Richardson" wrote:
> Anyone know how two join muliple records into 1.
>
> I have 3 tables like the Pubs DB.
>
> Authors
> Titles
> TitleAuthors
>
> This allows for many to many relationships.
>
> I need to be able to query this database so I can import titles & authors to
> a system that does not deal with many to many relations ships & so want the
> query to return the authors in one colum like below.
>
>
> title_id fulltitle author1
> title_id fulltitle author1, author2, author3
> title_id fulltitle author2, author3
> etc..
> etc..
>
> I would imagine there would be someway of doing this in a function or a
> cursor.
>
> Many Thanks
> Steve
>
>
>
- Next message: Rizwan: "Re: Need an option setting"
- Previous message: David Portas: "RE: Select statement"
- In reply to: Steven Richardson: "Select statement"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
