Decode Option Use(Like Pivot Table output)

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

Decode Option Use(Like Pivot Table output)

Author

Topic

karthik_192me
Starting Member

3 Posts

Posted - 2011-05-19 : 12:15:06

Hi

i have a challange to get this output,

my DB is

country| id |Plan
DE | XX1 | P
US | XX2 | W
US | XX3 | P
DE | XX4 | P

my output shd like

Plan|DE |US
W | 0 | 2
P | 2 | 1

i m using DECODE option, not getting exact output...Pl help me

SELECT plan,
count(DECODE(country,'DE',id)),
count(DECODE(c ountry,'US',id))
from MDL ORDER BY Plan;

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-05-19 : 12:21:54

The basic idea is this:


declare @t table
(country char(2), id char(3), [plan] char(1))
insert into @t
select 'DE' , 'XX1' , 'P' union all
select 'US' , 'XX2' , 'W' union all
select 'US' , 'XX3' , 'P' union all
select 'DE' , 'XX4' , 'P'

select	[plan], 
		sum(case when country='DE' then 1 else 0 end) [DE],
		sum(case when country='US' then 1 else 0 end) [US]
from @t
group by [plan]

This can be achieved by using PIVOT operator as well. If it needs to be dynamic based on varying 'country' then look into 'Dynamic Pivot' here on sqlteam.

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-19 : 12:22:02

well that's Oracle I believe, and this is a sql servewr site

Try here

http://www.dbforums.com/

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources