I have a 7 fields; MON, TUES, WED, THURS, FRI, SAT, & SUN which contain a 'Y' or 'N'. I would like to aggregate this information into one field within my select statement. I would like to place a 'M', 'T', 'W', 'R', 'F', 'S', and 'U', respectively, into my aggregated field if a 'Y' is found in the corresponding day field.

So if the MON & WED fields are 'Y' my final result would be 'MW' in my aggregated field.

What is the best way to code this in T-SQL; using case or some other means? I have tried to do this with a CASE statement but in T-SQL the first time a condition is met control jumps out of the CASE statement. This is unlike other languages where you have to explicitlyescape the CASE.

Thanks,
Steve

Please try:

DECLARE@TESTTABLE(MONCHAR(1)

,TUESCHAR(1)

,WEDCHAR(1)

,THURSCHAR(1)

,FRICHAR(1)

,SATCHAR(1)

,SUNCHAR(1))

INSERTINTO@TEST

SELECT'Y','Y','N','Y','Y','N','Y'

SELECTCASEMONWHEN'Y'THEN'M'ELSE''END

+CASETUESWHEN'Y'THEN'T'ELSE''END

+CASEWEDWHEN'Y'THEN'W'ELSE''END

+CASETHURSWHEN'Y'THEN'R'ELSE''END

+CASEFRIWHEN'Y'THEN'F'ELSE''END

+CASESATWHEN'Y'THEN'S'ELSE''END

+CASESUNWHEN'Y'THEN'U'ELSE''END

FROM@TEST

• Marked as answer by seiden Friday, February 20, 2009 4:36 PM

You can try this: DECLARE@testTABLE(

IDINTIDENTITY(1,1)

,MonBIT

,TueBIT

,WedBIT

,ThuBIT

,FriBIT

,SatBIT

,SunBIT)

INSERTINTO@test

(Mon,Tue,Wed,Thu,Fri,Sat,Sun)

SELECT1,1,1,1,0,0,1

UNION

SELECT0,0,1,1,0,0,1

UNION

SELECT1,1,1,1,1,1,1

UNION

SELECT0,0,0,0,0,0,0

SELECTCASEMonWHEN1THEN'M'ELSE''END

+CASETueWHEN1THEN'T'ELSE''END

+CASEWedWHEN1THEN'W'ELSE''END

+CASEThuWHEN1THEN'Th'ELSE''END

+CASEFriWHEN1THEN'F'ELSE''END

+CASESatWHEN1THEN'S'ELSE''END

+CASESunWHEN1THEN'SU'ELSE''END

FROM@test Your output will be: WThSU MTWThSU MTWThFSSU Thanks,

Tejas Shah

Thank youboth. Thesolution below is what I had come up with this morning without using a test table. The code you both provided is more straight forward. If I add a "as MTG_PAT" statement unto the end ofyourSELECT statement I can use this to insert the values into another table.

Thanks again,
Steve

DECLARE@MTG_PATVARCHAR(7),@MONVARCHAR(1),@TUESVARCHAR(1),@WEDVARCHAR(1),@THURSVARCHAR(1),@FRIVARCHAR(1),@SATVARCHAR(1),@SUNVARCHAR(1)

SELECT@MON='Y',@TUES='N',@WED='Y',@THURS='Y',@FRI='Y',@SAT='N',@SUN='N'

SELECT@MTG_PAT=''--Initializethevarable(removethenullvalue)

SELECT@MTG_PAT=CASEWHEN@MON='Y'THEN@MTG_PAT+'M'END

,@MTG_PAT=CASEWHEN@TUES='Y'THEN@MTG_PAT+'T'ELSE@MTG_PATEND

,@MTG_PAT=CASEWHEN@WED='Y'THEN@MTG_PAT+'W'ELSE@MTG_PATEND

,@MTG_PAT=CASEWHEN@THURS='Y'THEN@MTG_PAT+'R'ELSE@MTG_PATEND

,@MTG_PAT=CASEWHEN@FRI='Y'THEN@MTG_PAT+'F'ELSE@MTG_PATEND

,@MTG_PAT=CASEWHEN@SAT='Y'THEN@MTG_PAT+'S'ELSE@MTG_PATEND

,@MTG_PAT=CASEWHEN@SUN='Y'THEN@MTG_PAT+'U'ELSE@MTG_PATEND

SELECT@MTG_PAT

Need a case statement for the following business rule.

.( MD07 =$2.65 & md10= $ 6.50)
MD09 = $1.20 & $3.60----has the 2 values
MD03, MD04, MD05, MD06, & MD08 are the same for 2015.Everyother Value

Thanks.

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

This is minimal polite behavior on SQL forums. 

>> I have a 7 fields [sic]; MON, TUES, WED, THURS, FRI, SAT, & SUN which contain a 'Y' or 'N'. <<

WRONG. 

1. CASE is an expression and not a statement. 
2. Columns are not fields. 
3. The days of the week are not attributes; they are values on a temporal scale. And we usually use a two-letter abbreviation code or the ISO numeric 1-7 code. 
4. We do not use assembly language bit flags in RDBMS. Read: https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/

>> I would like to aggregate this information into one field [sic] within my select statement. I would like to place a 'M', 'T', 'W', 'R', 'F', 'S', and 'U', respectively, into my aggregated field [sic] if a 'Y' is found in the corresponding day field [sic]. So if the MON & WED fields [sic] are 'Y' my final result would be 'MW' in my aggregated field [sic]. <<

Why? This will destroy First Normal Form (1NF)! It would also mean that you are doing display formatting in the database. 

>> What is the best way to code this in T-SQL; using case or some other means? <<

CREATE TABLE Nameless_Something
(foobar_date DATE NOT NULL PRIMARY KEY, 
 week_day AS DATEPART(WK, foobar_date));

Now do the display formatting on the computed column. 

>> I have tried to do this with a CASE statement [sic] but in T-SQL the first time a condition is met control jumps out of the CASE statement. This is unlike other languages where you have to explicitly escape the CASE. <<

No, lots of languages outside of the C family use a switch like this. Look at Pascal, ADA and other higher level modern application languages as well as declarative languages. 

Most of the work in SQL is in the DDL, not the DML

Joe,

I came across your reply listed above just now.  My apologies for my very poor post. 

At first it's easy to say I've just been crushed by a zealot but there is no question that your points are valid.  I wish you would have used a little smaller hammer but I'm not sure if you meant to discourage me from the planet Earth or just being so sloppy when I have the occasion to work in SQL.

Anyway, your points are taken, though maybe not well.  I will endeavor to do a better job in the future. 

Thank you for taking the time to critique my post,

Steve

• Edited by seiden 14 hours 23 minutes ago

The rules of the basic Netiquette on SQL forums for the past 30+ years is that you post DDL, specs and sample data. 

We need the table names, column names, data types, keys, constraints and the DRI. How do you program without DDL? How can we read your mind? You need to learn ISO-8601 standards for temproal data, ISO-11179 for data elmemnts, and a lot of industry standards. Database is not easy. Why did you post an almost useless narragive?? 


There is no CASE statement in SQL; we have a CASE expression. 

>> I have a 7 fields [sic]; MON, TUE, WED, THU, FRI, SAT, & SUN which contain a 'Y' or 'N'. <<

More fundamental errors! If you read any book on SQL, you would know that a column is not a field; a field is part of a temporal value, {YEAR, MONTH, DAY,HOUR,MINUTE, SECOND}. Those column names are not valid; they are values on a temporal scale. 

We do not use assembly language flags in SQL. Our language is based on predicates and not flags. https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/. 

>>I would like to aggregate this information into one field [sic] within my select statement. I would like to place a 'M', 'T', 'W', 'R', 'F', 'S', and 'U', respectively, into my aggregated field [sic] if a 'Y' is found in the corresponding day field [sic]. So if the MON & WED fields [sic] are 'Y' my final result would be 'MW' in my aggregated field [sic]. <<

No, that would make this non-relational column non-atomic! You are trying to write a report complete with display fomatting! We do not do that; we write a query and pass it to a presentation layer. Do you have any idea what a tiered architecture is? 

>> What is the best way to code this in T-SQL; using CASE or some other means? I have tried to do this with a CASE statement [sic] but in T-SQL the first time a condition is met control jumps out of the CASE statement [sic]. This is unlike other languages where you have to explicitly escape the CASE. <<

Oh Ghod, no! SQL is a declaratve language, so there is no concept of a control flow. No jump, no escape or other proceudral baggage. 

I would write a query for you, but you did not post DDL. 

 first it's easy to say I've just been crushed by a zealot but there is no question that your points are valid.  I wish you would have used a little smaller hammer but I'm not sure if you meant to discourage me from the planet Earth or just being so sloppy when I have the occasion to work in SQL.

Getting a Keisaku from me is part of the SQL community ritual :) When I used smaller sticks, nobody heard me and kept posting the same mistakes over and over. They never read anything, looked a standard, etc and kept kludging their way thru life.

Based on teaching SQL for a few decades, I will guess you have less than 6 months of SQL programming and that your boss did not bother to get you any training. You are still writing your original procedural language using T-SQL.  This does not work.  "Before you can drink new tea, you must empty the old tea from your cup" Get a copy of "Thinking in Sets" see if it helps your epiphany.  

I think you missed the point of my last post.  I'm not looking for an answer to what I originally posted; only to say that your response makes sense and I appreciate that you took the time to say it. Thank you again. 

When I used smaller sticks, nobody heard me and kept posting the same mistakes over and over.

No one hears you now either because most of what you write either misses the point or is incorrect in the context of this message board.

Remember this place is for discussing T-SQL which is for better or worse not the same thing as ANSI standard SQL