SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 View with additional column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

spareus
Yak Posting Veteran

India
52 Posts

Posted - 01/10/2014 :  06:49:01  Show Profile  Reply with Quote
I have a view which shows below columns.
Type qty amount
p5 20 1000
er 1 50000
rr 2 200

I want to gave an additional column shown as final where data will be shown from other columns as below.
if type is er, newcol will show amount, or else newcol will show qty.
Pl note that this newcol in not present in any table.

Expected result as below.
Type qty amount newcol
p5 20 1000 20
er 1 50000 50000
rr 2 200 2

Pl help.
Thanks.


Regards,
Spareus

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/10/2014 :  06:57:16  Show Profile  Reply with Quote

CREATE VIEW# ViewNAme
AS
SELECT Type, qty, amount,CASE WHEN Type = 'er' THEN amount ELSE QTy END AS NewCol
FROM...

I assume amount and qty fields are of compatible datatypes otherwise you need to use CAST inside case when

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 01/10/2014 :  06:58:30  Show Profile  Visit webfred's Homepage  Reply with Quote
add this to your SELECT LIST directly before the FROM clause:

,CASE WHEN [Type] = 'er' THEN amount ELSE qty END as newcol




Too old to Rock'n'Roll too young to die.

Edited by - webfred on 01/10/2014 06:59:09
Go to Top of Page

spareus
Yak Posting Veteran

India
52 Posts

Posted - 01/10/2014 :  07:09:31  Show Profile  Reply with Quote
Perfact Visakh. Thanks a ton.


Regards,
Spareus
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/10/2014 :  07:10:10  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.36 seconds. Powered By: Snitz Forums 2000