代码之家 › 专栏 › 技术社区 › Gavin

SQL Server透视表帮助

pivot sql-server-2005 sql-server

1

Gavin · 技术社区 · 15 年前

我正试图将以下结果集

Meetings Covers   Date         TypeName
1         3       2010-10-14   Breakfast
1         1       2010-10-14   LunchCooked
2         4       2010-10-15   Breakfast
1         3       2010-10-18   Breakfast
1         3       2010-10-19   Breakfast
1         1       2010-10-19   LunchSandwich
1         3       2010-10-20   Breakfast
1         3       2010-10-21   Breakfast
1         3       2010-10-22   Breakfast

转换为包含以下字段的格式

Date
BreakfastMeetings
BreakfastCovers
LunchSandwichMeetings
LunchSandwichCovers
LunchCookedMeetings
LunchCookedCovers

我认为这可以用透视表完成吗?任何指针都是很好的,否则我将采取某种黑客临时表的路线,以获得这种格式的数据。

谢谢

1 回复 | 直到 10 年前

1

bobs 15 年前

这是一种方法。它实际上需要一个支点操作。unpivot将会议和封面合并为一列,并将TypeName值更改为所需的列名。

pivot使用unpivot的结果来提供最终格式。

SELECT TheDate, BreakfastMeetings, BreakfastCovers, LunchSandwichMeetings,
    LunchSandwichCovers, LunchCookedMeetings, LunchCookedCovers
FROM (
    -- unpivot to put counts in single column and create new type names
    SELECT TheDate, TypeName + MeetingCover AS TypeName, RowCounts
    FROM (SELECT TheDate, TypeName, Meetings, Covers
        FROM MyTable
        ) AS p
        UNPIVOT (RowCounts FOR MeetingCover IN (Meetings, Covers)
        ) AS UnpivotTable
    ) AS Source
    -- pivot the unpivoted data to create new columns
    PIVOT ( MAX(RowCounts) FOR TypeName IN (BreakfastMeetings, BreakfastCovers,
                                            LunchSandwichMeetings,
                                            LunchSandwichCovers,
                                            LunchCookedMeetings,
                                            LunchCookedCovers)
) AS PivotTable
ORDER BY TheDate