Return Multiple Rows from Column Value

Question

I need to create an XML from my stored procedure. My database has multiple values stored in a single row like the following:

ScheduleID      EmployeeID  M   Tu  W   Th  F   Sa  Su
======================================================
10              10001       1   1   0   0   0   0   0
11              10001       0   0   0   1   0   0   0
12              10002       0   0   0   0   0   1   1

I need to create an XML that would look like the following:

<schedules>
  <task>
    <ScheduleID>10</ScheduleID>
    <EmployeeID>10001</EmployeeID>
    <Workday>Monday</Workday>
  </task>
  <task>
    <ScheduleID>10</ScheduleID>
    <EmployeeID>10001</EmployeeID>
    <Workday>Tuesday</Workday>
  </task>
    <task>
    <ScheduleID>11</ScheduleID>
    <EmployeeID>10001</EmployeeID>
    <Workday>Thursday</Workday>
  </task>
  <task>
    <ScheduleID>12</ScheduleID>
    <EmployeeID>10002</EmployeeID>
    <Workday>Saturday</Workday>
  </task>
  <task>
    <ScheduleID>12</ScheduleID>
    <EmployeeID>10002</EmployeeID>
    <Workday>Sunday</Workday>
  </task>
</schedules>

The problem that I'm having is in order to do the above I would need to return return a different row for each day of the week. So I was thinking of using a case statement, however, the closest I come is the following which wouldn't work since it creates multiple columns named "Workday". Since there could be anywhere from 1-7 days that are valid for each ScheduleID, adding ELSE wouldn't be an option either. How can I go about doing this?

SELECT ScheduleID, EmployeeID,
    CASE WHEN (M = 1) THEN 'Monday' END as Workday,
    CASE WHEN (Tu = 1) THEN 'Tuesday' END as Workday,
    CASE WHEN (W = 1) THEN 1 ELSE 0 END as Workday,
    CASE WHEN (Th = 1) THEN 1 ELSE 0 END as Workday,
    CASE WHEN (F = 1) THEN 1 ELSE 0 END as Workday,
    CASE WHEN (Sa = 1) THEN 1 ELSE 0 END as Workday,
    CASE WHEN (Su = 1) THEN 1 ELSE 0 END as Workday
FROM Schedules
FOR XML PATH('task'), ROOT('schedules')

M.Ali · Accepted Answer · 2015-08-25 23:23:11Z

Test Data

DECLARE @T TABLE (ScheduleID INT, EmployeeID INT ,  M INT, 
                  Tu INT, W INT,  Th INT, F INT,  Sa INT, Su INT)
INSERT INTO @T VALUES 
(10 ,10001  ,1 ,  1 ,  0 ,  0 ,  0 ,  0 ,  0),
(11 ,10001  ,0 ,  0 ,  0 ,  1 ,  0 ,  0 ,  0),
(12 ,10002  ,0 ,  0 ,  0 ,  0 ,  0 ,  1 ,  1)

Query

;WITH X AS 
 (
SELECT *
 FROM (
    SELECT ScheduleID
          ,EmployeeID
          ,NULLIF(M , 0) AS Monday
          ,NULLIF(Tu, 0) AS Tuesday
          ,NULLIF(W , 0) AS Wednesday
          ,NULLIF(Th, 0) AS Thursday
          ,NULLIF(F , 0) AS Friday
          ,NULLIF(Sa, 0) AS Saturday
          ,NULLIF(Su, 0) AS Sunday
    FROM @T
    )t 
 UNPIVOT (Vals FOR Workday IN (Monday,Tuesday,Wednesday,Thursday
                                                    ,Friday,Saturday,Sunday))up
  )
SELECT ScheduleID
      ,EmployeeID
      ,Workday
FROM X 
FOR XML PATH('task'), ROOT('schedules')

Result:

<schedules>
  <task>
    <ScheduleID>10</ScheduleID>
    <EmployeeID>10001</EmployeeID>
    <Workday>Monday</Workday>
  </task>
  <task>
    <ScheduleID>10</ScheduleID>
    <EmployeeID>10001</EmployeeID>
    <Workday>Tuesday</Workday>
  </task>
  <task>
    <ScheduleID>11</ScheduleID>
    <EmployeeID>10001</EmployeeID>
    <Workday>Thursday</Workday>
  </task>
  <task>
    <ScheduleID>12</ScheduleID>
    <EmployeeID>10002</EmployeeID>
    <Workday>Saturday</Workday>
  </task>
  <task>
    <ScheduleID>12</ScheduleID>
    <EmployeeID>10002</EmployeeID>
    <Workday>Sunday</Workday>
  </task>
</schedules>

Collectives™ on Stack Overflow

Return Multiple Rows from Column Value

1 Answer 1

Test Data

Query

Result:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Test Data

Query

Result:

Comments

Your Answer

Sign up or log in

Post as a guest

Related