CODEWITHSUNDEEP
sql-serversql-server-2008for-xml-path
aantiix
aantiix

Reputation: 534

Return Multiple Rows from Column Value

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')

Upvotes: 3

Views: 56

Answers (1)

M.Ali
M.Ali

Reputation: 69494

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>

Upvotes: 2

Related Questions