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

SQL Server Development (2000)

QUERY

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-01-12 : 05:02:28

Table records :
-------------------
ID STATUS DATE
1 CL JAN1
1 OP FEB1
2 CL MAR1
2 OP APR1

OUTPUT SHOULD BE
------------------
ID CL_DATE OP_DATE
1 JAN1 FEB1
2 MAR1 APR1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-12 : 05:25:05

try this
select id, case when status = 'cl' then date end as 'cl_status', case when status = 'op' then date end as 'op_status'
from @tab

-- it will not work in 2000 so
works in 2005
select id,[cl] ,[op]
from @tab
pivot(max(date)for status in ([op],[cl])) pvt

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-12 : 05:37:29

Hi try this

declare @table Table (id int,status varchar(88),date varchar(777))
insert into @table
select 1, 'CL', 'JAN1' union all
select 1, 'OP', 'FEB1' union all
select 2, 'CL','MAR1' union all
select 2, 'OP', 'APR1'

select id,max(cldate),max(opdate) from
(select id ,case when status = 'cl' then date else null end as cldate,case when status = 'op' then date else null end as opdate from @table) t group by id

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-12 : 05:43:45

select id,
max( case when status = 'cl' then date else null end) as cldate,
max(case when status = 'op' then date else null end)as opdate
from @table group by id

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-01-12 : 05:45:36

HI JAY ,
IT WORKS FINE ...
but i could not understand how max(). group works in this scenario ???

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-01-12 : 05:48:23

If the status is having empty date mean it is not displaying ...

declare @table Table (id int,status varchar(88),date varchar(777))
insert into @table
select 1, 'CL', 'JAN1' union all
select 1, 'OP', 'FEB1' union all
select 2, 'CL','MAR1' union all
select 2, 'CL','' union all
select 2, 'OP', 'APR1'
SELECT * FROM @table
select id ,
case when status = 'cl' then date else null end as cldate,
case when status = 'op' then date else null end as opdate
from @table

select id,max(cldate),max(opdate) from
(select id ,
case when status = 'cl' then date else null end as cldate,
case when status = 'op' then date else null end as opdate
from @table) t group by id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources