CODEWITHSUNDEEP
sqlsql-servert-sqlweekday
Adil Farhan Farhan
Adil Farhan Farhan

Reputation: 19

How to repeat rows based on different columns in SQL Server

I have table with columns Monday, Tuesday, Wednesday, Thursday and Friday and are of Boolean type now I want to make a query to convert these rows to single column Weekday. It Should show me name of day of which column is true.

Upvotes: 1

Views: 103

Answers (3)

shA.t
shA.t

Reputation: 16968

Note: I think you mean bit type instead of Boolean that accepts only 0/1.

A mathematical way can be this:

select 
    datename(weekday,
       Monday + Tuesday*2 + Wednesday*3 + Thursday*4 + Friday*5 - 1) as weekDayName
from 
    t;

[ SQL Fiddle Demo ]

Side Note:

datename(weekday, 0) => Monday
datename(weekday, 1) => Tuesday
datename(weekday, 2) => Wednesday
datename(weekday, 3) => Thursday
datename(weekday, 4) => Friday
datename(weekday, 5) => Saturday
datename(weekday, 6) => Sunday / datename(weekday, -1) => Sunday

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522050

As pointed out, SQL Server does not have a boolean type, but it does have a BIT type which can only take on two values, 0 and 1. I am assuming that you are using a bit column. I also assume that only one day will be selected in each row. With both of these assumptions, you just need a single CASE expression:

SELECT
    CASE WHEN Monday = 1    THEN 'Monday'
         WHEN Tuesday = 1   THEN 'Tuesday'
         WHEN Wednesday = 1 THEN 'Wednesday'
         WHEN Thursday = 1  THEN 'Thursday'
         WHEN Friday = 1    THEN 'Friday' END AS day_selected
FROM yourTable

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You can use case. To get a comma-delimited list in SQL Server:

select t.*,
       stuff( ((case when Monday <> 0 then ',Monday' else '' end) +
               (case when Tuesday <> 0 then ',Tuesday' else '' end) +
               . . .
              ), 1, 1, ''
            ) as weekdays
from t;

I don't know how you specify "true" values. The above assumes that non-zero values are true.

Upvotes: 0

Related Questions