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
 General SQL Server Forums
 New to SQL Server Programming
 SQL 2008 Syntax Question

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 PLAN

123456789 VISA
123456789 MASTERCARD
123456789 AMEX

123456790 VISA
123456790 MASTERCARD

123456791 AMEX



I 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 OUTPUT
123456789 AMEX
123456790 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 acctype
from ACCOUNT_TABLE


==============================
I'm here to learn new things everyday..
Go to Top of Page

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]
Go to Top of Page

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_table
group by
account_number;[/CODE]Lists the account_number once.

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 00:33:02
to be safe

select
account_number,
CASE WHEN sum(
case when PLAN = 'AMEX'
then 1 ELSE 0 END) > 0 THEN 'AMEX' ELSE 'NON-AMEX' END
from
Account_table
group by
account_number;


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

- Advertisement -