possibility with Group By Function

Question

possibility with Group By Function

Meera

SSCommitted

Points: 1966
More actions
August 9, 2010 at 1:20 am

#141646

Hi all !!
May my question be of stupidity, but plz let me clarify the possibility. Thanks in advance !!
I wanna know the possibility of passing parameter to Group By function ..
Like after the query,
Group by @Item
kindly bear my innocence.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

If i am understanding your requirement correctly you want to keep the GROUP BY Clause Dynamic. For this you can use a Dynamic Query like given below

DECLARE@strGroupByClause VARCHAR(1000)

DECLARE@strSQL VARCHAR(8000)

SET@strGroupByClause = 'Col1,Col2,Col3' -- List the columns here

SET@strSQL = ' SELECT ' + @strGroupByClause + ', COUNT(*) NumberOfEntries '

+ ' FROM TableName '

+ ' GROUP BY ' + @strGroupByClause

EXECUTE( @strSQL )

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

No, you can not, unless you are using Dynamic SQL..

If u want to use variables to decide the column u want to aggregate your result set , i can give u an example ; follow this:

declare @table table (col1 char(1) , col2 char(2), amount int )

insert into @table (col1,col2, amount)

select 'A' ,'A', 100

union all select 'B' ,'A', 200

union all select 'C' ,'A', 300

union all select 'D' ,'A', 400

union all select 'E' ,'A', 500

union all select 'A' ,'B', 200

union all select 'B' ,'B', 300

union all select 'C' ,'B', 400

union all select 'D' ,'B', 500

declare @col int

set @col = 1

select

case

when @col = 1 then col1

when @col = 2 then col2

end col_chosen

, sum(amount) sm

from @table

group by

case

when @col = 1 then col1

when @col = 2 then col2

end

Meera SSCommitted Points: 1966 More actions · Answer 3

Hey its wonderful tht a solution is being with my assumed condition..

now i have mingle both of your query to make my one to achieve and it was working well with query level.

I tried to make it as procedure but its throwing some error,

here it is

Create Procedure Testing

@grp1 VARCHAR(1000) = '[Tab1SalesHeader].No, [Tab1SalesLine].Description, [Tab1SalesLine].[Variant Code]',

@grp2 VARCHAR(1000) = '[Tab1SalesHeader].No, [Tab1Sales Line].Description',

@SQL Varchar(8000) = ('Select' + Case When @Col = 1 Then @grp1 When @Col = 2 Then @grp2 end +

', Convert(numeric(10,2), SUM([Tab1SalesLine].Quantity)) AS EXPR1' + ',Count(*)' + 'From

[Tab1SalesHeader] INNER JOIN [Tab1SalesLine] ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]' +

'Group By' + Case When @Col = 1 Then @grp1 When @Col = 2 Then @grp2 end)

@Col int

As

Execute (@SQL)

Can u plz tell me where its going wrong??

Thanks a lot...

rvasanth SSC Eights! Points: 873 More actions · Answer 4

Your logic is correct. Can you post the exact error, so that we can help you

Meera SSCommitted Points: 1966 More actions · Answer 5

Oh sure Thanks,

The error is as below

Msg 102, Level 15, State 1, Procedure Testing, Line 4

Incorrect syntax near '('.

Msg 137, Level 15, State 2, Procedure Testing, Line 9

Must declare the scalar variable "@SQL".

rvasanth SSC Eights! Points: 873 More actions · Answer 6

i have done little modification

Create Procedure Testing

AS

DECLARE @SQL VARCHAR(MAX)

DECLARE @grp1 VARCHAR(1000)

DECLARE @grp2 VARCHAR(1000)

DECLARE @Col VARCHAR(10)

DECLARE @SELECTLIST VARCHAR(MAX)

SET @Col= 2

SET @grp1= '[Tab1SalesHeader].No, [Tab1SalesLine].Description, [Tab1SalesLine].[Variant Code]'

SET @grp2= '[Tab1SalesHeader].No, [Tab1Sales Line].Description'

IF (@Col=1)

SET @SELECTLIST = @grp1

ELSE

SET @SELECTLIST = @grp2

SET @SQL = 'Select ' +@SELECTLIST +'

, Convert(numeric(10,2), SUM([Tab1SalesLine].Quantity)) AS EXPR1,Count(*) From

[Tab1SalesHeader] INNER JOIN [Tab1SalesLine] ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]' +

'Group By ' + @SELECTLIST

EXEC(@SQL)

Thanks

R.Vasanth

http://dotnetcodeguide.blogspot.com

Meera SSCommitted Points: 1966 More actions · Answer 7

Vasanth !! Extraordinary !!! It worked !!

When i executed no error was thrown and the procedure was created.

Actually i wish to pass the Col value as parameter that the user should enter the value of the Col field..

Can you plz say me how could i do that ??

Thanks a million for helping out !!!

rvasanth SSC Eights! Points: 873 More actions · Answer 8

Create Procedure Testing

(

@Col int

)

AS

DECLARE @SQL VARCHAR(MAX)

DECLARE @grp1 VARCHAR(1000)

DECLARE @grp2 VARCHAR(1000)

//DECLARE @Col VARCHAR(10)

DECLARE @SELECTLIST VARCHAR(MAX)

//SET @Col= 2

SET @grp1= '[Tab1SalesHeader].No, [Tab1SalesLine].Description, [Tab1SalesLine].[Variant Code]'

SET @grp2= '[Tab1SalesHeader].No, [Tab1Sales Line].Description'

IF (@Col=1)

SET @SELECTLIST = @grp1

ELSE

SET @SELECTLIST = @grp2

SET @SQL = 'Select ' +@SELECTLIST +'

, Convert(numeric(10,2), SUM([Tab1SalesLine].Quantity)) AS EXPR1,Count(*) From

[Tab1SalesHeader] INNER JOIN [Tab1SalesLine] ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]' +

'Group By ' + @SELECTLIST

EXEC(@SQL)

Meera SSCommitted Points: 1966 More actions · Answer 9

Hi ! Thanks for your help !!

Again now i need to pass a date parameter too, so i did modified the procedure still as below,

Create Procedure Testing

(

@Date Datetime

@Col int

)

AS

DECLARE @SQL VARCHAR(MAX)

DECLARE @grp1 VARCHAR(1000)

DECLARE @grp2 VARCHAR(1000)

//DECLARE @Col VARCHAR(10)

DECLARE @SELECTLIST VARCHAR(MAX)

//SET @Col= 2

SET @grp1= '[Tab1SalesHeader].No, [Tab1SalesLine].Description, [Tab1SalesLine].[Variant Code]'

SET @grp2= '[Tab1SalesHeader].No, [Tab1Sales Line].Description'

IF (@Col=1)

SET @SELECTLIST = @grp1

ELSE

SET @SELECTLIST = @grp2

SET @SQL = 'Select ' +@SELECTLIST +'

, Convert(numeric(10,2), SUM([Tab1SalesLine].Quantity)) AS EXPR1,Count(*) From

[Tab1SalesHeader] INNER JOIN [Tab1SalesLine] ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]

Where [Tab1SalesHeader].OrderDate <'+IsDate(@Date) +

'Group By ' + @SELECTLIST

EXEC(@SQL)

But on Execution :

EXEC Testing '20100101', 2

gives error like,

Conversion failed when converting the varchar value 'Select[Tab1SalesHeader].No, [Tab1Sales Line].Description, SUM([Tab1SalesLine].Quantity)) AS EXPR1,Count(*) From

[Tab1SalesHeader] INNER JOIN [Tab1SalesLine] ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]

Where [Tab1SalesHeader].OrderDate <' to data type int

how to overcome this ?? Thanks for your kind help !!

rvasanth SSC Eights! Points: 873 More actions · Answer 10

rvasanth

SSC Eights!

Points: 873

August 10, 2010 at 12:43 am

#1204509

Do casting

i,e <' Cast(date, varchar(25))

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 11

To be precise

CAST (date AS VARCHAR)

-- OR

CONVERT (date , VARCHAR)

Meera SSCommitted Points: 1966 More actions · Answer 12

Meera

SSCommitted

Points: 1966

August 10, 2010 at 3:33 am

#1204586

Please any one reply !!!

Meera SSCommitted Points: 1966 More actions · Answer 13

Sorry for the previous message..

Now when executing its throwing,

Exec Testing '20100101', 1

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near '1'.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 14

Please can we see your stored procedure definition?

Thanks

John