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.
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 amountp5 20 1000er 1 50000rr 2 200I 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 newcolp5 20 1000 20er 1 50000 50000rr 2 200 2Pl help.Thanks.Regards,Spareus |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-10 : 06:57:16
|
[code]CREATE VIEW# ViewNAmeASSELECT Type, qty, amount,CASE WHEN Type = 'er' THEN amount ELSE QTy END AS NewColFROM...[/code]I assume amount and qty fields are of compatible datatypes otherwise you need to use CAST inside case when------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|