represent the data differently

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 2000 Forums

Transact-SQL (2000)

represent the data differently

Author

Topic

nbs
Starting Member

22 Posts

Posted - 2007-05-24 : 14:48:31

Hi all,
I have this information but i need to put it in a different format as stated below.
For a specific nbr and ID, when PIN store the rptdate as EMP_rpt and the contactdate as EMP_cont; likewise for CMT --> CMT_rpt and CMT_cont..below is an example how things should add up.

nbr ID role rptdate contactdate

90-04424-01; 200; CMT; 2006-12-10; 2006-12-11;
90-04424-01; 200; PIN; 2006-12-09; 2006-12-10;
90-04424-02; 206; CMT; 2007-01-02; 2007-01-08;
90-04424-02; 206; PIN; 2007-01-10; 2007-01-11;
90-04424-03; 208; PIN; 2007-02-10; 2007-03-10;
90-04424-03; 208; CMT; 2007-02-10; NULL;

nbr ID EMP_rpt EMP_cont CMT_rpt CMT_cont
90-04424-01; 200; 2006-12-09; 2006-12-10; 2006-12-10; 2006-12-11;
90-04424-02; 206; 2007-01-10; 2007-01-11; 2007-01-02; 2007-01-08;
90-04424-03; 208; 2007-02-10; 2007-03-10; 2007-02-10; NULL;

Can anyone help me on this one?
the initial set of data sits in a table.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-24 : 19:14:19

[code]
select nbr, ID,
EMP_rpt = max(case when role = 'PIN' then rptdate end),
EMP_cont = max(case when role = 'PIN' then contactdate end),
CMT_rpt = max(case when role = 'CMT' then rptdate end),
CMT_cont = max(case when role = 'CMT' then contactdate end)
from yourtable
group by nbr, ID
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-26 : 03:21:23

Also read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

nbs
Starting Member

22 Posts

Posted - 2007-05-29 : 11:06:42

quote:

Originally posted by khtan


select nbr, ID,
       EMP_rpt  = max(case when role = 'PIN' then rptdate end),
       EMP_cont = max(case when role = 'PIN' then contactdate end),
       CMT_rpt  = max(case when role = 'CMT' then rptdate end),
       CMT_cont = max(case when role = 'CMT' then contactdate end)
from   yourtable
group by nbr, ID

It worked.. thanks...but why do we need to have max() in each of these assignments above? any particular reason?

-nbs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources