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)
 View with additional column

Author  Topic 

spareus
Yak Posting Veteran

52 Posts

Posted - 2014-01-10 : 06:49:01
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

52326 Posts

Posted - 2014-01-10 : 06:57:16
[code]
CREATE VIEW# ViewNAme
AS
SELECT Type, qty, amount,CASE WHEN Type = 'er' THEN amount ELSE QTy END AS NewCol
FROM...
[/code]
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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-01-10 : 06:58:30
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.
Go to Top of Page

spareus
Yak Posting Veteran

52 Posts

Posted - 2014-01-10 : 07:09:31
Perfact Visakh. Thanks a ton.


Regards,
Spareus
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 07:10:10
welcome

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

- Advertisement -