sql server data warehouse: 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...
[quoted text, click to view]