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 |
funk.phenomena
Posting Yak Master
121 Posts |
Posted - 2013-06-17 : 16:02:04
|
Hi All - I have a table as such below (very very simplified version): [CODE]ACC_NUMBER PLAN123456789 VISA123456789 MASTERCARD123456789 AMEX123456790 VISA123456790 MASTERCARD123456791 AMEXI need it to output each individual account number per line, along with a field indicating if the account number has any instance of AMEX on it ....ACC_NUMBER OUTPUT123456789 AMEX123456790 NON-AMEX 123456791 AMEX How can I achieve this? Thanks!SELECT ACC_NUMBER, PLAN FROM ACCOUNT_TABLE T1[/code] |
|
shan007
Starting Member
17 Posts |
Posted - 2013-06-17 : 16:17:47
|
Try this,SELECT ACCOUNT_NUMBER, case when PLAN='AMEX' then PLAN ELSE 'NON-AMEX' END acctypefrom ACCOUNT_TABLE==============================I'm here to learn new things everyday.. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-17 : 16:47:47
|
[CODE]SELECT T1.ACC_NUMBER, (CASE WHEN T2.ACC_NUMBER IS NULL THEN 'NON-AMEX' ELSE 'AMEX' END) AS [OUTPUT] FROM (SELECT ACC_NUMBER FROM @Table1 GROUP BY ACC_NUMBER) T1 LEFT JOIN (SELECT ACC_NUMBER FROM @Table1 WHERE [PLAN] = 'AMEX') T2 ON T1.ACC_NUMBER = T2.ACC_NUMBER;[/CODE] |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-06-17 : 16:49:53
|
[CODE]select account_number, min( case when PLAN = 'AMEX' then 'AMEX' ELSE 'NON-AMEX' END )from Account_tablegroup by account_number;[/CODE]Lists the account_number once.=================================================The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 00:33:02
|
to be safeselect account_number, CASE WHEN sum( case when PLAN = 'AMEX' then 1 ELSE 0 END) > 0 THEN 'AMEX' ELSE 'NON-AMEX' END from Account_tablegroup by account_number; ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|