Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    71

    Unanswered: alternative Group By statements in a query

    Is there way to write a query in a stored procedure so that a parameter can be use to determin which 'group by' statement will be used? I'm thinking that CASE might do it. If so, can someone give me a working example of some SQL code that does this?

    Thanks,
    Clive

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Question Group by based on parameter

    Something along these lines should do it - this is a very basic example ive knocked up on the fly - might be worth you posting a bit more info for a more detailed solution such as your SQL but this should give you one option...

    CREATE PROCEDURE [ST_GROUPBY_EXAMPLE]

    --Group by based on parameter
    @YOURCRITERIA as nvarchar(20)='yourtable.Name'

    AS

    SELECT yourtable.Name, yourtable.Age, yourtable.neotherfields
    FROM yourtable
    GROUP BY @YOURCRITERIA

    GO
    --I FAIL############################

    <Sorry guys, I was rushing and SHOULD NOT have posted this without testing; (Slap wrists)

    I usually do this on the client app by starting with a string which has the main SQL and just has @CRITERIA (or similer) where the group by or where clauses are, then the client replaces @CRITERIA within the string whichever group by (or other criteria) is required based on a parameter sent to the code (This could be nothing if you like). Once the SQL string is formed, I then assign the SQL to the query object (Say pass-through query in Access front-end) this seems to work pretty well in my environment.

    Please accept my apology for being such a doochbag - if the above is of interest I will produce a working example TEST IT and post further details.
    Last edited by garethdart; 06-16-08 at 10:29.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That will fail gareth,

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think it will fail anyone else who tries it, too

    see http://www.theglobeandmail.com/servl.../Business/home
    Last edited by r937; 06-16-08 at 09:21.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Very good indeed Rudy - I hope you are as pleased as punch with yourself

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The answer, BTW, is not really. You could use dynamic SQL for what you describe. You could also use CASE expressions to sort of fudge multiple group by clauses but it would be ugly and cumbersome.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    or use a series of IFs, each with its own complete query featuring a different GROUP BY clause

    and poots, b0rk is as b0rk does
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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