sql-server - 从列值返回多行

Question

我需要从我的存储过程创建一个 XML。我的数据库有多个值存储在一行中，如下所示:

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

我需要创建一个如下所示的 XML:

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

我遇到的问题是为了执行上述操作，我需要为一周中的每一天返回不同的行。所以我在考虑使用 case 语句，但是，我最接近的是以下行不通的，因为它创建了多个名为“Workday”的列。由于每个 ScheduleID 可能有 1-7 天有效，因此添加 ELSE 也不是一个选项。我该怎么做呢？

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

Answer 1

测试数据

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)

查询

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

结果:

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