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.

  • Performance issue on selecting n newest rows in subselect
  • replace or remove characters and then cast to decimal
  • update values of sql table using the values from the same table
  • sql-server: how do i know who is in my database?
  • SQL CLR Programmability - Synchronization
  • SSRS - using a where clause for a placeholder
  • 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:

           Monday + Tuesday*2 + Wednesday*3 + Thursday*4 + Friday*5 - 1) as weekDayName

    [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:

        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.