How to convert a table into other form? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to convert a table into other form?

Author

Topic

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-08 : 13:49:53

Input:

user_id creative
196450 A
196450 B
196450 C
243011 B
243011 A
245789 A
245789 A

OutPut:
user_id # of times user # A creative #B creative #c creative
196450 3 1 1 1
243011 2 1 1 0
245789 2 3 0 0

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-04-08 : 13:51:22

I would use Crystal Reports.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 13:53:48

can you explain how count A became 3 for 245789?

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 13:54:20

Where did the 3 in the last output row come from? Is that a typo? Looks like it should be a 2.

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 13:56:47

anyways give this a go.


SELECT user_id,COUNT(creative) AS Total,
COUNT(CASE WHEN creative='A' THEN 1 ELSE NULL END) AS ATotal,
COUNT(CASE WHEN creative='B' THEN 1 ELSE NULL END) AS BTotal,
COUNT(CASE WHEN creative='C' THEN 1 ELSE NULL END) AS CTotal
FROM Table

GROUP BY user_id

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

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-08 : 13:57:19

196450 3(no of times user 196450 occurred) 1(A creative 1 time for this user) 1(B creative 1 time for this user) 1( C Creative 1 time for this user)

Does this make sens?

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-08 : 13:58:27

I think you got it! Thanks. You are awesome!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 14:00:07

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources