Help with Group By statement - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Group By statement

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2010-06-27 : 00:52:04

Hello.

I always have problems when it comes to grouping. I'm hoping you can help :)

This Query returns the following data:

TSQL:
select quizid, quizname, categoryname from userscores
where len(categoryname) > 0

RESULTS:

QuizID QuizName CategoryName
3031 Property & Casualty-Terms PROPERTY INSURANCE
2876 Workers Comp, Crime, etc. CASUALTY INSURANCE
3031 Uniform Policy Provisions DISABILITY INSURANCE
3088 Disability Definitions DISABILITY INSURANCE
3233 Types of Life Policies LIFE INSURANCE
2983 Health Plans (ex. HSA, HRA) - Health Plans (ex. HSA, HRA) DISABILITY INSURANCE

So there are 6 different quiz names, under 3 different categories.

I'd like the return data set to look like this:

DISABILITY INSURANCE (Category Name)
- QuizName
- QuizName
- QuizName

CASUALTY INSURANCE
- QuizName
- QuizName
- QuizName

PROPERTY INSURANCE
- QuizName
- QuizName
- QuizName

Does this make sense? I just want to group by the CATEGORYNAME, and then display the data grouped as such.

Can i do this with SQL alone, or is there a front end component to doing this kind of grouping?

I'm using ASP for this particular app btw.

Thanks!

- shawn

By the way, here is the table DDL:

USE [SSS]
GO
/****** Object: Table [dbo].[UserScores] Script Date: 06/26/2010 21:52:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserScores](
[userid] [int] NOT NULL,
[correct] [int] NOT NULL,
[total] [int] NOT NULL,
[percentage] [int] NOT NULL,
[quizid] [int] NULL,
[quizName] [varchar](350) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[subCategoryId] [int] NULL,
[categoryName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Sachin.Nand

2937 Posts

Posted - 2010-06-27 : 05:13:34

SQL is not meant for formatting the data.Formatting of data should be done at the front end.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources