Group By problem

Hi,

I need help on constructing a SQL statement that will produce me the following result:

The table is:

TABLE sample
F1 F2
-- --
1 a
1 b
2 c
2 d

select F1, ? what group function?
from sample
group by F1

Result should be:
F1 xxx
-- ---
1 a,b
2 c,d

Thanks for all the help.select F1,F2 from sample group by f1 order by f1|||Originally posted by kharsa60
select F1,F2 from sample group by f1 order by f1

I don't think this is possible since F2 is not a group by function. I still tried it though and got the following error message:

Column 'sample.f2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Any other suggestions?

Thanks.|||using pl/sql and or java you can do this quite easily. pseudo code;

rs1="select distinct(f1) from x order by f1"
while rs1.next
{
display rs1.f1
rs2="select f2 from x where f1=rs1.f1 order by f2"
while rs2.next
{
display rs2.f2
}
}|||I believe the only way you will get your output is with a cursor. This example is against PUBS database.
DECLARE @.pline varchar(200),
@.title_id tid,
@.cur_title tid,
@.au_id id

SET @.pline = ''

DECLARE out_cur CURSOR
READ_ONLY
FOR
SELECT title_id,
au_id
FROM titleauthor
ORDER BY
title_id

OPEN out_cur

FETCH NEXT FROM out_cur INTO @.title_id, @.au_id
SET @.cur_title= @.title_id

WHILE (@.@.fetch_status <> -1) BEGIN
IF (@.@.fetch_status <> -2) BEGIN
IF ( @.cur_title=@.title_id )
SET @.pline = @.pline + @.au_id + ', '
ELSE BEGIN
PRINT @.cur_title + ' : ' + SUBSTRING(@.pline,1,LEN(@.pline)-2)
SET @.pline = @.au_id + ', '
SET @.cur_title= @.title_id
END
END
FETCH NEXT FROM out_cur INTO @.title_id, @.au_id
END

CLOSE out_cur
DEALLOCATE out_cur
GO

The PL/SQL code that rhs98 showed can be done using ADO and VB,VBScript,Perl,etc. However I believe the output is not in the format that you want, it looks like the output would be a list.

F1
F2
F2
F1
F2
F2
F1
F2|||yeah ok, i see now, easily fixed though (that was only psuedo code btw). just don't use a println use a print; so the subsequent output stays on the same line.

or if that is a problem, you could just build up the output using a string or a buffer, and then display it before moving to the next loop.

I think this may be quicker to implement, but slightly slower than the pl/sql?|||guess it's really which way you know best; pl/sql or java...|||Considering that this is Microsoft SQL Server, PL/SQL is out and T-SQL is in.|||Thanks all. I see that there is no way of doing it via SQL statement. So I'll just going to do it in java then, since I am actually using java. I just thought I can do it with one SQL statement.

Thanks a lot.|||ooooooops :D
missed that one... :rolleyes: