Looping through records

Hi,
I have a table with papers and second table with types of papers. Now
beetwen papers and types there is many to many relationship.
But in my application I would like to display records the way:
eg.
paper1, type1 type2 ... typex
paper2, type2
paper3, type3 type5
..
I don't know how to create view, procedure or function to create loop
through types for one paperid.
something like
for i = 0 to counter(numeber of records for paperid)
string_types = string_types + type
next record
Thank you
*** Sent via Developersdex http://www.examnotes.net ***Look at some examples that I'd not relied on it :-))))
Seriously, such kind of reports you will be better doing on the client side
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+coalesce(t,'')+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
<schapopa> wrote in message news:uw4Wrl8%23FHA.272@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with papers and second table with types of papers. Now
> beetwen papers and types there is many to many relationship.
> But in my application I would like to display records the way:
> eg.
> paper1, type1 type2 ... typex
> paper2, type2
> paper3, type3 type5
> ..
> I don't know how to create view, procedure or function to create loop
> through types for one paperid.
> something like
> for i = 0 to counter(numeber of records for paperid)
> string_types = string_types + type
> next record
> Thank you
> *** Sent via Developersdex http://www.examnotes.net ***|||schapopa (schapopa) writes:
> I have a table with papers and second table with types of papers. Now
> beetwen papers and types there is many to many relationship.
> But in my application I would like to display records the way:
> eg.
> paper1, type1 type2 ... typex
> paper2, type2
> paper3, type3 type5
> ..
> I don't know how to create view, procedure or function to create loop
> through types for one paperid.
> something like
> for i = 0 to counter(numeber of records for paperid)
> string_types = string_types + type
> next record
> Thank you
In SQL 2000, the only reliable way to do this is to run a cursor. Uri showed
you an example with a function, but that relies an undefined behaviour, and
may not produce the correct result.
In SQL 2005, there is syntax for this, thanks to the XML support. (It's
somewhat obscure, but it works.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your help. I used function that Uri showed me as an
example. It works properly.
Cheers
Schapopa
*** Sent via Developersdex http://www.examnotes.net ***|||Thank you for your help. I used function that Uri showed me as an
example. It works properly.
Cheers
Schapopa
*** Sent via Developersdex http://www.examnotes.net ***