One of my users has requested that I change the way I display the cost elements associated with a particular booking. Instead of merely listing the cost elements, this user would like me to produce a table that, in his opinion, provides a clearer breakdown of the booking requirements.The new table needs to show the total quantity of each cost element required at each rate and on each day, as Table 1 shows. In this example, SQL Server generates a column for each date from the minimum (\[Start_Date\]) to the maximum (\[Start_Date\] + \[Duration\]) and generates a row for each Room_Type-Rate_Quoted pair, inserting the total Quantity for the pair at the intersection of each date column.
My database currently contains a table (BKG_Stock) that stores cost elements against a booking and uses the stored procedure that Listing 1 shows to create the list of cost elements. I use the SQL statement that Listing 2 shows with a stored procedure to query Listing 1's stored procedure and return a list of cost elements for a particular booking.
For a description and example of how to accomplish the change you describe, see the Microsoft article "How to Rotate a Table in SQL Server," at http://support .microsoft.com/default.aspx?scid=kb;en-us; 175574.The article provides an explanation, examples of tables showing the change, and sample queries for regular and large tables. Note that SQL Server 2005 has an explicit PIVOT command.To use either the method that the article describes or the SQL Server 2005 PIVOT command, you have to know your columns when you call for the data to be pivoted so that you can prevent a dynamic reaction to the new data that would cause the creation of a new column. If your data is dynamic, you should think about building a dynamic SQL statement and using the sp_executesql stored procedure or an EXEC statement to execute it.