Please help

Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
matching values.
Tab3 should have the info as below.
Tab1
ID Val1 Val2
1 qqq sa
2 aaa fa
3 ddd we
Tab2
ID F1
1 A
1 B
1 C
1 D
2 F
2 G
3 H
Output Should be as blelow, without using the functions please advise the
sql query.
Tab3
ID Val1 Val2 F1
1 qqq sa A,B,C,D
2 aaa fa F,G
3 ddd we H
Thanks in advance.
It's not all that pretty, but the following function will do the job,
there may be more efficient approaches, but this one does work.
--============================================
CREATE FUNCTION JoinRows(@.id int)
RETURN varchar(50)
AS
BEGIN
DECLARE @.f1 char(1)
DECLARE @.str varchar(50)
SET @.f1 = ''
SET @.str = ''
WHILE 1=1
BEGIN
SELECT top 1 @.f1 = f1
,@.str = @.str + f1 + ','
FROM tab2
WHERE id = @.id
AND f1 > @.f1
IF @.@.rowcount = 0 BREAK
END
IF LEN(@.str) > 1
SET @.str = SUBSTRING(@.str,1,len(@.str)-1)
RETURN @.str
END
--============================================
-- This select query will then return Tab3
SELECT
*, dbo.JoinRows(id)
FROM tab1
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <u4oLJnfxFHA.736@.tk2msftngp13.phx.gbl>, dkrreddy@.hotmail.com
says...
> Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
> matching values.
> Tab3 should have the info as below.
> Tab1
> --
> ID Val1 Val2
> 1 qqq sa
> 2 aaa fa
> 3 ddd we
>
> Tab2
> --
> ID F1
> 1 A
> 1 B
> 1 C
> 1 D
> 2 F
> 2 G
> 3 H
>
> Output Should be as blelow, without using the functions please advise the
> sql query.
> Tab3
> --
> ID Val1 Val2 F1
> 1 qqq sa A,B,C,D
> 2 aaa fa F,G
> 3 ddd we H
>
> Thanks in advance.
>
>
|||The following will work as well:
declare @.f1str varchar(100)
set @.f1str = ''
select @.f1str = @.f1str + f1 + ','
from F1
select @.f1str
You'd have to run it an ID at a time in a function but at least it's
only one execution per ID as opped to F1.
Hope that helps.