How to replace and combine the values from multiple columns in T-SQL

user_12298841 0
Viewed 305

I have a table in SQL Server 2016 that holds the scheduling of meetings across the weekdays. It stores the days in a column for each day of the week (examples below). I am wanting to query this table and get the results in a single column in a more user-friendly format.

Currently the data is stored in the table like this:

SCHEDULE TABLE:
MEETING_TITLE | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY
FOO           | NULL   | Y       | NULL      | Y        | NULL

My desire is to combine these weekday columns in the results, and convert them to the abbreviation for the respective days.

Desired result:

SCHEDULE TABLE:
MEETING_TITLE | MEETING_DAYS
FOO           | T/THU

I have looked into case statements, and if/else and have tried to work with declaring variables to hold the values, but nothing has worked so far. To demonstrate my attempt at solving I specifically tried to declare a variable to hold the formatted days. After that I tried to do an if/else for each day looking for 'Y' in the column. If found, I hoped to do a += kind of thing to combine the values as needed. Everything I tried just resulted in errors and the query wouldn't run.

I appreciate any help on this.

(The above example is of course simplified, this table is far more complex than the above)

sql-server-2016 tsql sql-server sql
6 Answers
--You can use CASE Statements to concat and then
--a combination of STUFF and REVERSE to remove the trailing / off [MEETING_DAYS]
SELECT [MEETING_TITLE], reverse(stuff(reverse(
CASE WHEN [Monday] IS NULL THEN '' ELSE 'Mon/' END + 
CASE WHEN [Tuesday] IS NULL THEN '' ELSE 'T/' END + 
CASE WHEN [Wednesday] IS NULL THEN '' ELSE 'Wed/' END + 
CASE WHEN [Thursday] IS NULL THEN '' ELSE 'THU/' END + 
CASE WHEN [Friday] IS NULL THEN '' ELSE 'Fri/' END), 1, 1, '')) AS [MEETING_DAYS]
FROM [dbo].[Schedule]
user_48301960

This version only works in SQL Server 2017 or above (or if you use a custom string agg function).

Another possibly is cross apply:

select s.*, v.*
from schedule s cross apply
     (select string_agg(name, '/') within group (order by ord) as meeting_days
      from (values ('M', Monday, 1),
                   ('T', Tuesday, 2),
                   ('W', Wednesday, 3),
                   ('Thu', Thursday, 4),
                   ('F', Friday, 5)
           ) v(name, val, ord)
      where val = 'Y'
     ) v;
user_11440350

With sample data:

Declare @testData table (
        meeting_title varchar(20)
      , Monday        char(1)
      , Tuesday       char(1)
      , Wednesday     char(1)
      , Thursday      char(1)
      , Friday        char(1)
        );



 Insert Into @testData (meeting_title, Monday, Tuesday, Wednesday, Thursday, Friday)
 Values ('FEE', Null, 'Y', Null, 'Y', Null)
      , ('FIE', 'Y', Null, 'Y', 'Y', Null)
      , ('FOE', 'Y', 'Y', Null, Null, 'Y');

 Select *
      , meeting_days = stuff(concat('/' + iif(td.Monday    = 'Y', 'Mon', Null)
                                  , '/' + iif(td.Tuesday   = 'Y', 'Tue', Null)
                                  , '/' + iif(td.Wednesday = 'Y', 'Wed', Null)
                                  , '/' + iif(td.Thursday  = 'Y', 'Thu', Null)
                                  , '/' + iif(td.Friday    = 'Y', 'Fri', Null)), 1, 1, '')
                                  
   From @testData td;
user_153949000

A simple way to accomplish, if you were using SQL 2017 or later, would be

select meeting_title,
    Concat_Ws('/',
        Iif(monday is null,null,'Mon'),
        Iif(tuesday is null,null,'tue'),
        Iif(wednesday is null,null,'wed'),
        Iif(thursday is null,null,'thu'),
        Iif(friday is null,null,'fri')
    ) as meeting_days
from schedule
user_153326500

Please try the following solution.

Notable points:

  • We are tokenizing columns of interest as XML.
  • FOR XML ... automatically filters out columns with NULL values.
  • Starting 3 chars are used to abbreviate each day of week.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (
    ID INT IDENTITY PRIMARY KEY, 
    meeting VARCHAR(100),
    MONDAY CHAR(1),
    TUESDAY CHAR(1),
    WEDNESDAY CHAR(1),
    THURSDAY CHAR(1),
    FRIDAY CHAR(1)
);
INSERT INTO @tbl (meeting,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY) VALUES
('FOO', NULL,'Y',NULL,'Y',NULL),
('New Meeting', 'Y','Y',NULL,'Y',NULL);
-- DDL and sample data population, end

SELECT ID, meeting
    , REPLACE(c.query('
        for $x in /root/*
        return substring(local-name($x),1,3)
    ').value('.', 'VARCHAR(100)'), SPACE(1),'/') AS MEETING_DAYS
FROM @tbl
CROSS APPLY (SELECT MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY
    FOR XML PATH(''), TYPE, ROOT('root')) AS t(c);

Output

+----+-------------+--------------+
| ID |   meeting   | MEETING_DAYS |
+----+-------------+--------------+
|  1 | FOO         | TUE/THU      |
|  2 | New Meeting | MON/TUE/THU  |
+----+-------------+--------------+
user_19323110

NOTE: This is Oracle syntax not sql-server. Leaving it here anyway for others who might like this as an oracle answer.

select meeting_title
    , CONCAT( decode( monday, 'Y', 'M' ),' ',
      decode( tuesday, 'Y', 'T' ),' ',
      decode( wednesday, 'Y', 'W' ),' ',
      decode( thursday, 'Y', 'Th' ),' ',
      decode( friday, 'Y', 'F' ) )
from my_schedule
user_3187490
Related