SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL 2008 Syntax Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funk.phenomena
Posting Yak Master

Canada
120 Posts

Posted - 06/17/2013 :  16:02:04  Show Profile  Reply with Quote
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


Edited by - funk.phenomena on 06/17/2013 16:13:04

shan007
Starting Member

USA
17 Posts

Posted - 06/17/2013 :  16:17:47  Show Profile  Reply with Quote
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

547 Posts

Posted - 06/17/2013 :  16:47:47  Show Profile  Reply with Quote


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;

Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 06/17/2013 :  16:49:53  Show Profile  Reply with Quote
select
   account_number, 
   min(
      case when PLAN = 'AMEX'
      then 'AMEX'
      ELSE 'NON-AMEX'
      END
      )
from
   Account_table
group by
   account_number;
Lists the account_number once.

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

Edited by - Bustaz Kool on 06/17/2013 16:52:46
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/18/2013 :  00:33:02  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000