CODEWITHSUNDEEP
sql-serversql-server-2005pivot
Gavin
Gavin

Reputation: 17392

SQL Server Pivot Table Help

I'm trying to turn the following ResultSet

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

Into a format with the following fields

Date
BreakfastMeetings
BreakfastCovers
LunchSandwichMeetings
LunchSandwichCovers
LunchCookedMeetings
LunchCookedCovers

Am I right in thinking this can be done with pivot tables? Any pointers would be great otherwise I'm going to end up taking some sort of hacky temp table route to get the data into this format.

Thanks

Upvotes: 1

Views: 200

Answers (1)

bobs
bobs

Reputation: 22204

Here's a way to do it. It actually needs and unpivot and a pivot operation. The unpivot combines the Meetings and Covers into a single column and changes the TypeName values to the desired column names.

The pivot uses the results of the unpivot to provide the final format.

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

Upvotes: 1

Related Questions