Group count results to columns not rows

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 2008 Forums

Transact-SQL (2008)

Group count results to columns not rows

Author

Topic

alexdski
Starting Member

3 Posts

Posted - 2010-11-17 : 12:09:27

I'm a fast learning beginner and know this must be common, I've searched for the answer but guess I just don't know what to ask for.

I've created a select query that counts values into groups as follows:

SELECT Category, Status, COUNT(Status) AS Count
FROM [SPH:Helpdesk]
GROUP BY Category, Status
ORDER BY Category

which outputs something like:

Category || Status || Count
IT || Open || 4
IT || Closed || 488
IT || With Supplier || 7
New Item || Open || 43
New Item || Closed || 75

Is it possible to append the grouped results to columns, or pivot the results in T-SQL?

What I really want to output is

Category || Open || Closed || With supplier
IT || 4 || 488 || 7
New Item || 43 || 75

I do apologise if I am being insanely naive for the level I'm aiming for but I've been playing with SQL 2008 for a week now and my books havent even arrived yet.

Thank you

Alex

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-11-17 : 12:20:17

Look into the PIVOT operator. A google search on T-SQL PIVOT Example will get you good results.

It will look something like this:

SELECT *
FROM [SPH:Helpdesk]
PIVOT
(
COUNT(*)
FOR [Status] IN ([Open], [Closed], [With supplier])
)
AS p

The downside of PIVOT is that you need to know the values you are going to pivot when you are writing the query. If that list is going to change, you can use dynamic SQL to overcome that, but there are other downsides to that approach.

alexdski
Starting Member

3 Posts

Posted - 2010-11-17 : 13:02:38

Thanks very much, thats exactly what I'm looking for.

I'm getting errors about Pivot grouping columns must be comparable for a column i'm not interested, even when I select only the columns I want.

I'm sure I'll find answers to this now I know what to look for.

Thanks again. SQL is awesome!

alexdski
Starting Member

3 Posts

Posted - 2010-11-17 : 19:20:53

If any other beginners get the same error...

I append to a temp table with just the fields I want (to pivot) and the following works perfectly...

SELECT *
FROM [Helpdesk]
PIVOT
(
COUNT(ID)
FOR [Status] IN ([Open], [Closed],[Awaiting User])
)
AS p

Thanks again Lazareth

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-11-17 : 20:18:54

quote:
Originally posted by alexdski
I append to a temp table with just the fields I want (to pivot) and the following works perfectly...

you can use CTE or derived table instead of a temp table


SELECT *
FROM 
(
    SELECT Category, ID, Status, . . .
    FROM   [SPH:Helpdesk]
)
AS h
PIVOT
(
    COUNT(ID)
    FOR [Status] IN ([Open], [Closed],[Awaiting User])
)
AS p

KH
[spoiler]Time is always against us[/spoiler]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-18 : 11:34:20

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. What you have is a mix of reserved words and names so vague as to be useless. Remember 80-90% of the work in SQL is in the DDL and not in DML.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

CREATE TABLE Helpdesk
(job_ticket INTEGER NOT NULL PRIMARY KEY,
job_category VARCHAR(15) NOT NULL
CHECK (job_category IN (..)),
job_status VARCHAR(15) DEFAULT 'Open' NOT NULL
CHECK (job_status IN ('Open', 'Closed', 'With Supplier'),
..);

Every table needs a key by definition. There is no such things as a generic, universal "status" or "category" -- these are properties of an attribute. By using CHECK() constraints, you guarantee data integrity and never have to program them into ALL of your DML statements.

SELECT job_category,
SUM(CASE WHEN job_status = 'Open' THEN 1 ELSE 0 END) AS open_cnt,
SUM(CASE WHEN job_status = 'Closed' THEN 1 ELSE 0 END) AS closed_cnt,
SUM(CASE WHEN job_status = 'With Supplier'
THEN 1 ELSE 0 END) AS supplier_cnt
FROM Helpdesk
GROUP BY job_category;

This use of SUM() and CASE expressions is a common idiom in SQL. It avoids proprietary reporting tools like PIVOT and has a lower overhead.

Since you are new to SQL, you might want to get a copy of THINKING IN SETS to get over the switch from a procedural mindset to a set-oriented mindset.

--CELKO--
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources