passing valus in the group by clause

Question

Post reply

passing valus in the group by clause

urbanromio2

Say Hey Kid

Points: 664
More actions
December 3, 2016 at 3:28 am

#315560

declare @colnames as varchar(max)
set @colnames = ( SELECT STUFF((
SELECT ', ' + name
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.combinations')
ORDER BY column_id
FOR XML PATH('')
), 1, 2, '') )
select * from combinations group by cube (@colnames )
i need to pass values in the group by clause cube functions which is column names . but in my table, the column names are dynamic and but this does not accept referece like i given .
how can i solve this?
the erorr is,
Each GROUP BY expression must contain at least one column that is not an outer reference.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

It needs to be dynamic SQL. Details in the code below. Don't ignore the SQL Injection prevention.

--===== This could be a parameter passed to a stored procedure.

DECLARE @pTableName VARCHAR(257);

SELECT @pTableName = 'dbo.Combinations '

;

--===== Make sure no SQL Injection came in as a table name.

-- Intentionally return no data that an attacker could use

-- to help figure out an attack.

IF OBJECT_ID(@pTableName) IS NULL

RETURN

;

--===== Local Variables

DECLARE @ColNames VARCHAR(MAX)

,@SQL VARCHAR(MAX) --Added this

;

--===== Create the dynamic SQL using the trick of cascading-variables to simplify.

SELECT @ColNames =

STUFF(

(

SELECT ',' + QUOTENAME(name) --QUOTENAME to safeguard against weird column names

FROM sys.columns

WHERE object_id = OBJECT_ID(@pTableName)

ORDER BY column_id

FOR XML PATH('')

)

,1,1,'')

,@SQL = REPLACE(REPLACE( --Pretested code that's been "tokenized" for simplicity.

'SELECT * FROM <<@pTableName>> GROUP BY CUBE (<<@ColNames>>);'

,'<<@pTableName>>',@pTableName)

,'<<@ColNames>>' ,@ColNames) --Uses content created in this same SELECT above.

;

--===== Display the SQL that would be executed.

-- This could be removed for Production.

PRINT @sql

;

--===== Execute the dynamic SQL

EXEC (@SQL)

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

urbanromio2 Say Hey Kid Points: 664 More actions · Answer 2

urbanromio2

Say Hey Kid

Points: 664

December 3, 2016 at 10:23 am

#1915895

thank u bro

Joe Celko SSCertifiable Points: 5749 More actions · Answer 3

in my table, the column names are dynamic ..

SQL does not work this way. By definition, the columns of a table are known and constant and it is time of creation. You are trying to fake an old file system in SQL and performance will suck rocks. We make fun of people with systems like this and refer to such things as the "automobiles, squids and Lady Gaga" table (or procedure or anything else in SQL that requires unknown structure). When I look at a column in your non-data model, it makes me want to ask, "on a scale from 1 to 10 what color is your favorite letter of the alphabet?"

If you cannot get a consistent spec that fits into the relational model, then you are stuck with dynamic SQL which is the way you admit failure of your design or choice of RDBMS as a solution.

What are you actually trying to do?

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

urbanromio2 Say Hey Kid Points: 664 More actions · Answer 4

I have data in a column.

Want to make all the possible combinations.

You have given the idea that group by cube will do that,

And have i have to pass arguments which are column names into the cube() function.

But i have data in the column,

So pivoting the table, so the columns are dynamic now..

Im stucking here now :p

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

CELKO (12/3/2016)
in my table, the column names are dynamic ..
SQL does not work this way. By definition, the columns of a table are known and constant and it is time of creation.

"In theory, theory and practice are the same. In practice, they are not." There are many remarkable methods for doing the "impossible" in SQL Server if you don't have such a limited imagination. If you live only by the "definitions of others", your process will either die by their definitions are be swamped in a "Tower of Babel".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgmunson SSC Guru Points: 110640 More actions · Answer 6

urbanromio2 (12/3/2016)
thank u bro

I may not agree with Mr. Celko's perspective, but I do think that if you have enough columns, you'll eventually have a "scale" problem. I'm also curious as to how well such a construct will actually perform. Is this the same overall problem as your other posts, where you're looking to gather all possible combinations of metal pieces, knowing the weight and purity, in order to blend them and get a specific weight result and purity?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)