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.

  • CASE WHEN statement for ORDER BY clause
  • How do you get the last record generated in a recursive CTE?
  • Data Auditing in NHibernate and SqlServer
  • SQL Server 2012 via native C++ (no ATL) preferred access method from Windows
  • Exclude rows with a column containing a value if multiple rows exist for
  • SQL Server stored procedure to insert in multiple tables
  • 3 Solutions collect form web for “How to repeat rows based on different columns in SQL Server”

    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
    

    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.

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.