query help? - 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 2008 Forums

Transact-SQL (2008)

query help?

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-07-03 : 18:22:17

Hi friends

I have the following table
Customers with following fields

ID,Counter,Name
regular ,8,John
Weekly,10,John,
Monthly,9,John

I want to write a query for getting the resukts as follows

Name,regularcount,Weeklycount,Monthlycount
John,8,10,9

How can I achieve this..Thank you in advance...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-03 : 20:28:43

You can use the PIVOT operator if you know the ID values in advance.

SELECT
	*
FROM
	YourTable
PIVOT
( SUM(counter) FOR ID IN ([regular],[Weekly],[Monthly]))p

If you don't know the ID's in advance, you will need to more work - for example, using dynamic pivot. See Madhivanan's blog for code and examples: http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-07-04 : 17:31:08

thank you sunita beck...it works the way i want..but i need one more help

my id has one more value called daily

ID,Counter,Name
regular ,8,John
Weekly,10,John,
Monthly,9,John
Daily,2,John

so my query now shud be liek this
Name,regularcount,Weeklycount,Monthlycount
John,11(regular+daily),10,9

Can i achieve this..thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-04 : 18:51:22

[code]
SELECT Name,
[regular] + [Daily] AS regularcount,
[Weekly] as weeklycount,
[Monthly] as monthlycount
FROM
YourTable
PIVOT
( SUM(counter) FOR ID IN ([regular],[Weekly],[Monthly],[Daily]))p
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources