Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2002
    Posts
    4

    Unanswered: 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.

  2. #2
    Join Date
    Mar 2002
    Posts
    10

    group

    select F1,F2 from sample group by f1 order by f1

  3. #3
    Join Date
    Mar 2002
    Posts
    4

    Re: group

    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.

  4. #4
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    using pl/sql and or java you can do this quite easily. pseudo code;

    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
      }
    }

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I believe the only way you will get your output is with a cursor. This example is against PUBS database.
    Code:
    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

  6. #6
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    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???

  7. #7
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    guess it's really which way you know best; pl/sql or java...

  8. #8
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Considering that this is Microsoft SQL Server, PL/SQL is out and T-SQL is in.

  9. #9
    Join Date
    Mar 2002
    Posts
    4
    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.

  10. #10
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    ooooooops
    missed that one....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •