假设我有一个名为Abilities的表，它看起来类似于
| DayOfWeek | Action | Strength |
|---|---|---|
| Sunday | Walk | Weak |
| Sunday | Walk | Strong |
| Sunday | Run | Strong |
| Monday | Walk | Strong |
我希望能够按Action和Strength进行分组，然后判断条目是否存在于一周中的每一天:
| Action | Strength | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
|---|---|---|---|---|---|---|---|---|
| Walk | Weak | Y | N | N | N | N | N | N |
| Walk | Strong | Y | Y | N | N | N | N | N |
| Run | Strong | Y | N | N | N | N | N | N |
我听说Pivot可能会做到这一点，但我想知道是否有可用的 Select ？谢谢.
我听说Pivot可以做到这一点，但我想知道是否有 有没有其他 Select ？
您可以这样做，如下所示:
SELECT
Action,
Strength,
MAX(CASE WHEN DayOfWeek = 'Sunday' THEN 'Y' ELSE 'N' END) AS Sunday,
MAX(CASE WHEN DayOfWeek = 'Monday' THEN 'Y' ELSE 'N' END) AS Monday,
MAX(CASE WHEN DayOfWeek = 'Tuesday' THEN 'Y' ELSE 'N' END) AS Tuesday,
MAX(CASE WHEN DayOfWeek = 'Wednesday' THEN 'Y' ELSE 'N' END) AS Wednesday,
MAX(CASE WHEN DayOfWeek = 'Thursday' THEN 'Y' ELSE 'N' END) AS Thursday,
MAX(CASE WHEN DayOfWeek = 'Friday' THEN 'Y' ELSE 'N' END) AS Friday,
MAX(CASE WHEN DayOfWeek = 'Saturday' THEN 'Y' ELSE 'N' END) AS Saturday
FROM Abilities
GROUP BY Action, Strength;