specify column field by condition

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

specify column field by condition

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2012-10-12 : 15:35:46

Hi All

I try to create select


select distinct
TestNo
,LName+', '+FName Name
,case when (p.Status_Id)=20 then (p.StatusDate) 
      when (p.Status_Id)=28 then (p.StatusDate) end Test_Date
,p.Status_Id
from dbo.Request m
join (select p1.* 
	  From dbo.Statuses p1
	  join (Select p2.Request_id, StatusDate 
		    From dbo.Statuses p2) mp
	  on p1.Request_Id = mp.Request_Id) p
on m.Request_Id = p.Request_Id

It works but I would like to have 2 column for StatusDate in that select:
if Status_Id = 20 StatusDate column should be Test1
if Status_Id = 28 StatusDate column should be Test2

How it to do?

Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-12 : 16:18:49

I'm not really following your question. Do you want two new columns names Test1 and Test2? If so, then maybe this will help:

    ,case when (p.Status_Id)=20 then (p.StatusDate) else null end AS Test1
    ,case when (p.Status_Id)=28 then (p.StatusDate) else null end AS Test2

eugz
Posting Yak Master

210 Posts

Posted - 2012-10-12 : 16:28:09

Hi Lamprey. Thanks for replay.

That works. I add at the end of select where p.StatusDate is not null to eliminate all row where Test1 and Test2 is null but didn't get result. What is problem?

Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-12 : 16:35:16

I can't say for sure, I can only assume that all the p.StatusDate values are NULL. If that is not the case, then you'll have to prodive DDl, DML and expected results:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources