How to use Group By with Dynamic Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to use Group By with Dynamic Columns

Author

Topic

ikhuram
Starting Member

28 Posts

Posted - 2004-05-27 : 06:10:21

If there are n columns in the table "A". And table A is joined with table B. While we have to use aggregate function on B.Column1 then how will we use the Group By for table A. e.g.,

select *, sum(B.quantity)
from A join B on
A.sequence = B.sequence
group by ????????????

NOTE: A.* is not fixed number of columns it could be 2 or 200.

noamg
Posting Yak Master

215 Posts

Posted - 2004-05-27 : 07:15:50

ONLY BY Dynamic-sql. means, build the query in real time and use the sp_sqlExec to run it.

Noam Graizer

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-27 : 07:39:36

never use a group by in that situation, use a subquery or a derived table.

select *,(select sum(quantity) from b where b.id=a.id) as Total
from a

or

select a.*, b.total
from a
inner join (select b.id, sum(quantity) as total from b group by id) b
on a.id=b.id

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-27 : 07:41:09

also, never use select * anyway; specify the column list.

- Jeff

ikhuram
Starting Member

28 Posts

Posted - 2004-05-27 : 08:53:57

Thanks for the Tip

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources