Grouping without GROUP BY? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Grouping without GROUP BY?

Author

Topic

leewebdev
Starting Member

3 Posts

Posted - 2007-03-14 : 09:24:35

Hi everyone! I'm new to the this forum, but have a little SQL background. Looking forward to learning a lot from everyone here.

I have a report grouping question, but I don’t think the GROUP BY command will work. Look at the sample table below:

Person FavColor
------ --------
David Red
Mike Black
John Red
Steve Red
Sam Black
Adam Purple

Currently, the above temp table is created via a SQL sproc and returned to a Cold Fusion web page and displayed as a simple table. What I’d like to do is have the display look like this:

BLACK
Mike
Sam

PURPLE
Adam

RED
David
John
Steve

My question is, is it possible to program SQL to output the above so I can just dump the output into the Web page, or do I have to program the Web page to take my current result set and somehow iterate through the table to pull out and display group headings?

I don’t need to show subtotals or grand totals, so I'm thinking GROUP BY will not work here. I’m using SQL 2000 and do NOT have Reporting Services.

Any help would be greatly appreciated. Thanks!

David

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 10:46:18

[code]-- Prepare sample data
DECLARE @Sample TABLE (Person VARCHAR(5), FavColor VARCHAR(6))

INSERT @Sample
SELECT 'David', 'Red' UNION ALL
SELECT 'Mike', 'Black' UNION ALL
SELECT 'John', 'Red' UNION ALL
SELECT 'Steve', 'Red' UNION ALL
SELECT 'Sam', 'Black' UNION ALL
SELECT 'Adam', 'Purple'

-- Show the expected result
SELECT CASE
WHEN RecID = 0 THEN UPPER(Person)
ELSE Person
END AS Person
FROM (
SELECT 1 AS RecID,
FavColor,
Person
FROM @Sample
UNION ALL
SELECT DISTINCT 0,
FavColor,
FavColor
FROM @Sample
UNION ALL
SELECT DISTINCT 2,
s1.FavColor,
''
FROM @Sample AS s1
WHERE s1.FavColor <> (SELECT MAX(s2.FavColor) FROM @Sample AS s2)
) AS x
ORDER BY FavColor,
RecID,
Person[/code]
Peter Larsson
Helsingborg, Sweden

leewebdev
Starting Member

3 Posts

Posted - 2007-03-14 : 11:07:39

Pure genius! Thanks so much Peter. The code is simple and was easy for me to modify. It tested flawlessly in my app. Thanks again.

David

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-14 : 11:16:46

leewebdev -- this type of formatting should always be done on your web page. It is very, very easy to do. SQL should just return the results -- formatting should never be done at the database layer.

What are you using? ASP? ASP.NET? It takes about 4-5 lines of code in those languages to do the formatting, and your sql is much shorter, faster, simpler, and more maintainable if you keep it focused on the *data* and not the formatting.

Peso -- Do you really think that you are providing good advice to someone just learning by suggesting that they do this in T-SQL ?

- Jeff
http://weblogs.sqlteam.com/JeffS

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-14 : 11:21:10

Pure Genius!

SELECT Person, FavColor
FROM myTable99
ORDER BY FavColor, Person

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 11:32:33

We do not know what the modifications are and I have not much knowledge of ColdFusion.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-14 : 11:37:04

quote:
Originally posted by X002548

Pure Genius!

SELECT Person, FavColor
FROM myTable99
ORDER BY FavColor, Person
Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Brett -- you just "optimized" the solution and made it about 4 times as fast! Amazing work!

Oh -- cold fusion. I missed that part, thanks for pointing it out. Not sure about cold fusion as well. But I am quite sure that you must have some sort of looping mechanism and can do a simple "if Favorite <> LastFavorite then output header" check .....

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-14 : 11:50:44

A quick google returned some info about creating groups in coldfusion, as well as other web programming frameworks:

http://docs.codecharge.com/studio3/html/index.html?http://docs.codecharge.com/studio3/html/ProgrammingTechniques/HowTo/SimpleReporting/SimpleReportWithGroupHeaders.html

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 11:54:16

Are you certain that next question from leewebdev is not about
"How to do looping and grouping in ColdFusion"
or "how do if Favorite <> LastFavorite work"?

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-14 : 12:07:16

that's fine, that is a perfectly good question to ask if they don't know -- They should learn how to do that if they will be using coldfusion.

And, of course, they should probably ask that simple question on a coldfusion forum.

And, I already gave them a link!

- Jeff
http://weblogs.sqlteam.com/JeffS

leewebdev
Starting Member

3 Posts

Posted - 2007-03-14 : 12:10:18

Good points. I do not know Cold Fusion -- I prefer ASP.net -- but that's what I'm forced to use in this case. I will research Cold Fusion loops. However, in the short term, I find it satisfying that I can produce grouping via SQL and maybe it will only be an issue with large record sets, I don't know. The one issue I am having now with the SQL example is if I want to return a few more fields in the SELECT statement; these additional fields are causing the DISTINCT call to breakdown. I'm getting close.

David

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-14 : 12:14:21

David -- I strongly suggest looking at the link I gave you. I do not know cold fusion either and it was pretty easy to follow along what you need to do.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources