Get Counts by Days and User - 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

Get Counts by Days and User

Author

Topic

MevaD
Starting Member

32 Posts

Posted - 2013-12-24 : 00:24:51

If I have a table like this:

ID User Date
20 22 1-1-2013
21 22 1-1-2013
22 31 1-1-2013
23 22 1-1-2013
24 22 1-2-2013
25 22 1-2-2013
etc...

How can I get the count of records grouped by date and user?

Date 22 31
1-1-2013 3 1
1-2-2013 2 0
etc...

Is this possible?

Thanks.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-12-24 : 00:56:46

SELECT *
FROM (SELECT [User], DATE, Id
FROM TableName
) pvt
PIVOT (COUNT(id) FOR [User] IN ([22], [31]))p

--
Chandu

MevaD
Starting Member

32 Posts

Posted - 2013-12-24 : 01:11:41

Thanks Chandu.

Can the user IDs be selected dynamically in the query? For example, the user IDs might be some other values for another date range.

PIVOT (COUNT(id) FOR [User] IN ([22], [31]))p

Thank you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-12-24 : 06:57:54

Here you go http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

MevaD
Starting Member

32 Posts

Posted - 2013-12-24 : 10:12:09

Perfect. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources