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

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

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.

spareus
Yak Posting Veteran

52 Posts

Posted - 2014-01-10 : 07:09:31

Perfact Visakh. Thanks a ton.

Regards,
Spareus

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources