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.

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.sql

Please advise

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.See: http://www.aspfaq.com/show.asp?id=2529
Razvan|||See: http://www.aspfaq.com/show.asp?id=2529
Razvan