How to return a LOT of other columns with group by

Question

How to return a LOT of other columns with group by

Viewing 6 posts - 16 through 20 (of 20 total)

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

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 1

Mark (1/19/2008)
Several ways to do this, here's one
...ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Factor1) as rn

One of the reasons I love these forums is that there's always something new to learn (or be reminded of). I've probably seen 'PARTITON BY' on BOL, but it's the first time I've seen a clear example of when it's really useful.

Thanks Mark.

kirk (1/19/2008)
This table is absolutely demoralized! I never would have designed something like this.

No, the table is DENORMALIZED, it's the DBA/developer who's demoralized! "You want me to import what???" 😉

Derek

Bob Fazio SSChampion Points: 10794 More actions · Answer 2

Also if the issue is how long it will take to construct the SQL with the 99 max's this should help.

DECLARE @SQL NVARCHAR(MAX)

DECLARE @tblName sysname,@GroupName sysname

SELECT @tblName = 'MASTER.dbo.tblTranLogs',

@GroupName = 'dbname'

SELECT @SQL = 'SELECT ' + QUOTENAME(@GroupName)

SELECT @SQL = @SQL + ', MAX('+QUOTENAME(NAME)+') as ' + QUOTENAME(NAME)

FROM MASTER.sys.columns

WHERE [object_id] = OBJECT_ID(@tblName)

AND NAME != @GroupName

SELECT @SQL = @SQL + ' FROM ' + @tblName + ' GROUP BY ' + QUOTENAME(@GroupName) + ' ORDER BY ' + QUOTENAME(@GroupName)

SELECT @sql

EXEC (@sql)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

..with the caveat that aggregate functions don't work against various datatypes like text, NTEXT, IMAGE or XML...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

kirk-510417 SSC Eights! Points: 809 More actions · Answer 4

I will have to spend some time with this one. I am not sure how it gets teh column names or placeses MAX on every column name. But if it saves me from having to type out a bazilen column names I'm all for it.

Thanks

Bob Fazio SSChampion Points: 10794 More actions · Answer 5

Change MASTER TO your DB name

@tblName = 'MASTER.dbo.tblTranLogs',

Change to the name of the column you want to group by

@GroupName = 'dbname'

kirk (1/24/2008)
I will have to spend some time with this one. I am not sure how it gets teh column names

FROM MASTER.sys.columns

or placeses MAX on every column name. But if it saves me from having to type out a bazilen column names I'm all for it.
Thanks

... MAX(...

The QUOTENAME() places [] around the column names. In case you did something like put spaces in your column names.

SELECT @SQL = @SQL + ', MAX('+QUOTENAME(NAME)+') as ' + QUOTENAME(NAME)

Also you might want to change the select to the following

SELECT @SQL = @SQL + '
, MAX('+QUOTENAME(NAME)+') as ' + QUOTENAME(NAME)
FROM MASTER.sys.columns

It adds a little more formatting to the resulting sql

And selecting from the code blocks sometimes doesn't help much.

This executes it, perhaps you might want to comment that out at first.

EXEC (@sql)

kirk-510417 SSC Eights! Points: 809 More actions · Answer 6

I have looked at dynamic SQL before so I think I have a handle on that. Your explanations of the details is very helpful. I will be able to get it going now. 🙂