Count of distinct types - 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

Count of distinct types

Author

Topic

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-02-07 : 12:29:08

Hi everyone , i think this is a fairly easy sql script but i cannot work it out , i have data in a table in the format

Dept Type

IT a
Finance a
IT a
Sales b
Sales c
Finance c
IT b

and need an output :

Dept Type a Type b Type c
IT 2 1 0
Sales 0 1 1
Finance 1 0 1

Any help greatly appreciated

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-07 : 16:19:24

[code]SELECT
Dept,
a AS [Type a],
b AS [Type b],
c AS [Type c]
FROM
TheTable
PIVOT (COUNT([type]) FOR [Type] IN ([a],[b],[c]))P;[/code]

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-02-08 : 05:29:56

Hi James, many thanks for your reply , will this work for SQL2000 ? .. tried it but got errors

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 05:37:18

quote:
Originally posted by emailuser

Hi James, many thanks for your reply , will this work for SQL2000 ? .. tried it but got errors

Nope PIVOT is available only from 2005 onwards

for 2000 you need to use


SELECT Dept,
SUM(CASE WHEN Type='a' THEN 1 ELSE 0 END) AS [Type a],
SUM(CASE WHEN Type='b' THEN 1 ELSE 0 END) AS [Type b],
SUM(CASE WHEN Type='c' THEN 1 ELSE 0 END) AS [Type c]
FROM Table
GROUP BY Dept

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-08 : 06:40:26

This is for dynamic number of types http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 07:08:20

quote:
Originally posted by madhivanan

This is for dynamic number of types http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

OP is still on 2000
so i think this is what he should be using for dynamic crosstabbing

http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-08 : 07:16:50

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

This is for dynamic number of types http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

OP is still on 2000
so i think this is what he should be using for dynamic crosstabbing

http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks. Then the link you posted is correct

Madhivanan

Failing to plan is Planning to fail

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-02-13 : 11:32:26

thanks everyone and to visakh16 for the solution .. just upgraded to sql2005 over weekend so a few more options now :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 11:33:59

ok...cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources