Multiple columns with case statement

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)

Multiple columns with case statement

Author

Topic

jemacc
Starting Member

42 Posts

Posted - 2006-11-10 : 14:01:30

I have a situation which I need assistance.
I want to use a case state that evaluates multiple columns
column1, colum2, column3, column4
Each columns holds different data

select column1=case
WHEN column1 In('00','01','02','40','55','80','85','95')THEN 'test'
WHEN column2 in( '56', '25', '27')then 'test2'
WHEN column3 in( '1', '2', '3')then 'test3'
ELSE 'NG'
END,
SELECT column4, column5, column6
from table1

The resul set should look something like this:
Column1 Column2 Column3 Column4 column5 column6
test test2 Test3 data1 data2 data3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 14:06:11

Drop the second select and the query should work as wished for.

select		case
			WHEN column1 In('00','01','02','40','55','80','85','95')THEN 'test'
			WHEN column2 in( '56', '25', '27')then 'test2'
			WHEN column3 in( '1', '2', '3')then 'test3'
			ELSE 'NG'
		END as column123,
		column4,
		column5,
		column6
from		table1

Peter Larsson
Helsingborg, Sweden

jemacc
Starting Member

42 Posts

Posted - 2006-11-10 : 14:28:31

OKAY,

I did not have the select in my original statement thanks.

This way the result will be store as column123,

I want to be able to run a case state on each column

separately.

column1= entity information
column2 =company information
column3=department information

Thanks for your help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-12 : 05:59:04

Then I think a WHERE is better suited.

select		column1,
		column2,
		column3,
		column4,
		column5,
		column6
from		table1
WHERE		column1 in ('00','01','02','40','55','80','85','95')
		and/or column2 in ('56','25','27')
		and/or column3 in ('1','2','3')

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-12 : 08:31:38

Here are my efforts:

Select
	(Case when column1 In ('0','01','02','40','55','80','85','95') THEN 'test' end) as Column1,
	(Case when column2 In ('56', '25', '27') then 'test2' End) as Column2,
	(Case when column3 In ('1', '2', '3') then 'test3' End) as Column3,
	column4, column5, column6
From table1

Harsh Athalye
India.
"Nothing is Impossible"

jemacc
Starting Member

42 Posts

Posted - 2006-11-13 : 12:34:29

Thank you so much but I am still having some issues as to how I am presenting the data.

Take look at this: How can I present the data with each case having its own column header. do I have to run each separate for each column.
These are the columns
SlsOriginSource,
DEPTSOURCE,
CompanySource,
SLSORIGIN

SELECT CASE

WHEN SlsOriginSource In('00','01','02','40','55','80','85','95') THEN 'DEF_Brand'
WHEN SlsOriginSource In('20')THEN 'FOF_Brand'
WHEN SlsOriginSource In('30')THEN 'RD_Brand'
WHEN SlsOriginSource In('05')THEN 'BW_Brand'
WHEN SlsOriginSource In('10')THEN 'CFG_Brand'
WHEN SlsOriginSource In('15')THEN 'PL_Brand'
WHEN SlsOriginSource In('22')THEN 'CC_Brand'
WHEN SlsOriginSource In('50')THEN 'TMTT_Brand'
WHEN SlsOriginSource In('60')THEN 'ST_Brand'
WHEN SlsOriginSource In('70')THEN '21_Brand'
WHEN SlsOriginSource In('75')THEN 'WIA_Brand'
WHEN SlsOriginSource In('90')THEN 'GA_BRAND'
WHEN SlsOriginSource In('25')THEN 'DYNETECH_BRAND'
WHEN SlsOriginSource In('2A')THEN '2A_Brand'
WHEN SlsOriginSource In('2B')THEN '2B_Brand'
WHEN SlsOriginSource In('2C')THEN '2C_Brand'
WHEN SlsOriginSource In('2D')THEN '2D_Brand'
WHEN SlsOriginSource In('2E')THEN '2E_Brand'
WHEN SlsOriginSource In('2F')THEN '2F_Brand'
WHEN SlsOriginSource In('2G')THEN '2G_Brand'
WHEN SlsOriginSource In('2H')THEN '2H_Brand'
WHEN SlsOriginSource In('2I')THEN '21_Brand'
WHEN SlsOriginSource In('2J')THEN '2J_Brand'
WHEN SlsOriginSource In('AU')THEN 'AU_Brand'
WHEN SlsOriginSource In('CD')THEN 'CD_Brand'
WHEN SlsOriginSource In('CC')THEN 'CC_Brand'
WHEN SlsOriginSource In('GR')THEN 'GR_Brand'
WHEN SlsOriginSource In('OL')THEN 'OL_Brand'
WHEN SlsOriginSource In('PC')THEN 'PC_Brand'
WHEN SlsOriginSource In('PM')THEN 'PM_Brand'
WHEN SlsOriginSource In('ST')THEN 'SP_Brand'
WHEN SlsOriginSource In('TG')THEN 'TG_Brand'
WHEN SlsOriginSource In('UK')THEN 'UK_Brand'
WHEN SlsOriginSource In('VN')THEN 'VN_Brand'
WHEN SlsOriginSource In('WC')THEN 'WC_Brand'
WHEN SlsOriginSource In('SI')THEN 'SI_Brand'
WHEN SlsOriginSource In('05')THEN 'BW_Brand'
WHEN SlsOriginSource In('05')THEN 'BW_Brand'
WHEN DEPTSOURCE IN ('00')THEN 'DEF_DEPT'
WHEN DEPTSOURCE IN ('10')THEN 'CC_DEPT'
WHEN DEPTSOURCE IN ('15')THEN 'SLC_DEPT'
WHEN DEPTSOURCE IN ('20')THEN 'JA_DEPT'
WHEN DEPTSOURCE IN ('22')THEN 'DEF_DEPT'
WHEN DEPTSOURCE IN ('24')THEN 'BOCA_DEPT'
WHEN DEPTSOURCE IN ('26')THEN 'ORL_DEPT'
WHEN DEPTSOURCE IN ('30')THEN 'PMG_DEPT'
WHEN DEPTSOURCE IN('35')THEN 'RECOV_DEPT'
WHEN DEPTSOURCE IN('40')THEN 'PCS_DEPT'
WHEN DEPTSOURCE IN('43')THEN 'DBAA_DEPT'
WHEN DEPTSOURCE IN('50')THEN 'zz_DEPT'
WHEN DEPTSOURCE IN('60')THEN 'UK_DEPT'
WHEN DEPTSOURCE IN('65')THEN 'UK_RECOV_DEPT'
WHEN DEPTSOURCE IN ('70')THEN 'SP_DEPT'
WHEN DEPTSOURCE IN ('75')THEN 'AUSTRL_DEPT'
WHEN DEPTSOURCE IN ('80')THEN 'CAN_DEPT'
WHEN DEPTSOURCE IN ('85')THEN 'GER_DEPT'
WHEN DEPTSOURCE IN ('88')THEN 'INTL_DEPT'
WHEN DEPTSOURCE IN ('90')THEN 'SING_DEPT'
WHEN DEPTSOURCE IN('95')THEN 'INTRL_DEPT'
WHEN DEPTSOURCE IN('FI')THEN 'FI_DEPT'
WHEN DEPTSOURCE IN('BK')THEN 'BK_DEPT'
WHEN DEPTSOURCE IN('CC')THEN 'CC_DEPT'
WHEN DEPTSOURCE IN('CD')THEN 'CAN_DEPT'
WHEN DEPTSOURCE IN('CO')THEN 'CO_DEPT'
--CN_DEPTSOURCE
WHEN DEPTSOURCE IN('CS')THEN 'CS_DEPT'
WHEN DEPTSOURCE IN('ED')THEN 'ED_DEPT'
WHEN DEPTSOURCE IN('EN')THEN 'EN_DEPT'
WHEN DEPTSOURCE IN('FM')THEN 'FM_DEPT'
WHEN DEPTSOURCE IN('HR')THEN 'HR_DEPT'
WHEN DEPTSOURCE IN('IS')THEN 'IS_DEPT'
WHEN DEPTSOURCE IN('IT')THEN 'IT_DEPT'
WHEN DEPTSOURCE IN('LA')THEN 'LA_DEPT'
WHEN DEPTSOURCE IN('LG')THEN 'LG_DEPT'
WHEN DEPTSOURCE IN('MM')THEN 'MM_DEPT'
WHEN DEPTSOURCE IN('MK')THEN 'MK_DEPT'
WHEN DEPTSOURCE IN('NM')THEN 'NM_DEPT'
WHEN DEPTSOURCE IN('OO')THEN 'OO_DEPT'
WHEN DEPTSOURCE IN('OP')THEN 'OP_DEPT'
WHEN DEPTSOURCE IN('PC')THEN 'PCS_DEPT'
WHEN DEPTSOURCE IN('PI')THEN 'PIN_DEPT'
WHEN DEPTSOURCE IN('PM')THEN 'PMG_DEPT'
WHEN DEPTSOURCE IN('RE')THEN 'RE_DEPT'
WHEN DEPTSOURCE IN('RP')THEN 'RP_DEPT'
WHEN DEPTSOURCE IN('SC')THEN 'SC_DEPT'
WHEN DEPTSOURCE IN('SL')THEN 'SL_DEPT'
WHEN DEPTSOURCE IN('SP')THEN 'SHP_DEPT'
WHEN DEPTSOURCE IN('SE')THEN 'SE_DEPT'
WHEN DEPTSOURCE IN('SA')THEN 'SA_DEPT'
WHEN DEPTSOURCE IN('SU')THEN 'SU_DEPT'
WHEN DEPTSOURCE IN('TS')THEN 'TS_DEPT'
WHEN DEPTSOURCE IN('VN')THEN 'DBAA_DEPT'
WHEN DEPTSOURCE IN('WC')THEN 'SLC_DEPT'
WHEN DEPTSOURCE IN('0-')THEN 'DEF_DEPT'
WHEN CompanySource IN('10') THEN 'WEG_ENTITY'
WHEN CompanySource IN('07') THEN 'SCB_ENTITY'
WHEN CompanySource IN('16')THEN 'WLGP_ENTITY'
WHEN CompanySource IN('25')THEN 'WPRE_ENTITY'
WHEN CompanySource IN('30')THEN 'WIA_ENTITY'
WHEN CompanySource IN('37')THEN 'MRS_ENTITY'
WHEN CompanySource IN('40')THEN 'WINTERNET_ENTITY'
WHEN CompanySource IN('45')THEN 'WC_ENTITY'
WHEN CompanySource IN('46')THEN 'WUK_ENTITY'
WHEN CompanySource IN('47')THEN 'WSP_ENTITY'
WHEN CompanySource IN('48')THEN 'WAUSTRL_ENTITY'
WHEN CompanySource IN('50')THEN 'WCONSLT_ENTITY'
WHEN CompanySource IN('55')THEN 'EDU_ENTITY'
WHEN CompanySource IN('57')THEN 'RD_ENTITY'
WHEN CompanySource IN('88')THEN 'WDEVEL_ENTITY'
WHEN CompanySource IN('89')THEN 'CA_ENTITY'
WHEN CompanySource IN('90')THEN 'WMORT_ENTITY'
WHEN CompanySource IN('95')THEN 'ELIM_ENTITY'
WHEN CompanySource IN('99')THEN 'WINTL_ENTITY'
WHEN CompanySource IN('05')THEN 'WINL_ENTITY'
WHEN SLSORIGIN IN ('00')THEN 'DEFLT_ORG'
WHEN SLSORIGIN IN ('0') THEN 'DEFLT_ORG'
WHEN SLSORIGIN IN ('30')THEN 'DEFLT_ORG'
WHEN SLSORIGIN IN ('50')THEN 'DEFLT_ORG'
WHEN SLSORIGIN IN ('FL')THEN 'DEFLT_ORG'
WHEN SLSORIGIN IN ('02')THEN 'RE_ORG'
WHEN SLSORIGIN IN ('GA')THEN 'GA_ORG'
WHEN SLSORIGIN IN ('HA')THEN 'HA_ORG'
WHEN SLSORIGIN IN ('RE')THEN 'RE_ORG'
WHEN SLSORIGIN IN ('TE')THEN 'RE_ORG'
WHEN SLSORIGIN IN ('TM')THEN 'TM_ORG'
WHEN SLSORIGIN IN ('TN')THEN 'TN_ORG'
WHEN SLSORIGIN IN ('TT')THEN 'TT_ORG'
WHEN SLSORIGIN IN ('3V')THEN '3V_ORG'
WHEN SLSORIGIN IN ('20')THEN 'FF_ORG'
WHEN SLSORIGIN IN ('10')THEN 'SW_ORG'
WHEN SLSORIGIN IN ('RI')THEN 'RI_ORG'
WHEN SLSORIGIN IN ('GS')THEN 'GS_ORG'
ELSE 'NC'
END ,LedgerID,
Module,
Account,
AccountDescription,
TransactionType,
ReferenceNumber,
TransactionDescription,
CreditAmount,
SubAccount,
--SlsOriginSource,--Separates Subaccount by Brand
--Dept,--Separates Subaccount by Dept
CompanySource,--Separates Subaccount by CompanySource
DebitAmount,
--A.PerPost as PeriodToPost,
--Substring(PerPost,1,4)+substring(PerPost,5,4 )as PeriodPost,
--Substring(PerPost,1,4)+'10'+datename(day,PerPost),
PeriodToPost,--Separates the date into Year and Month
SubaccountDescription,
BatchNumber,
TransactionDate
from account

All of this data is located in one table. If I am looking at this wrong. Please let me know.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-13 : 13:01:31

No.

If you want multiple columns, you would have to write separate CASE statements like this:

SELECT 
CASE WHEN SlsOriginSource In('00','01','02','40','55','80','85','95') THEN 'DEF_Brand' End  As Col1,
Case WHEN SlsOriginSource In ('20') THEN 'FOF_Brand' End as Col2,
Case WHEN SlsOriginSource In ('30') THEN 'RD_Brand' End as Col3
...

By the query you posted, you would get single column for entire case statement!

Also if you are just comparing with single value, you can use = operator instead of IN operator.

Harsh Athalye
India.
"Nothing is Impossible"

jemacc
Starting Member

42 Posts

Posted - 2006-11-13 : 14:14:04

Once again thank you. What will be the best method to combine each select statement against the same table. Any suggestion will be helpful.

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-13 : 14:55:59

quote:
Originally posted by jemacc

Once again thank you. What will be the best method to combine each select statement against the same table. Any suggestion will be helpful.

Read the hint link in my sticky for more info on how to post a question...

but this seems so wrong for so many reasons

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-11-13 : 16:36:48

It looks like the possible values for: brand, department, entity, and org need to be in tables, linked to SlsOriginSource, DEPTSOURCE, CompanySource, SLSORIGIN.

Trying to maintain data just in code is ... awkward, to say the least...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources