Convert database rows to columns

Question

I need to convert database rows to columns and show the result in a gridview. My DB is as follows:

ID Hotel cDate Price ----------------------------------------------- 1 Hotel1 12/22/2009 12:00:00 AM 15.0000 2 Hotel2 12/22/2009 12:00:00 AM 25.0000 3 Hotel3 12/22/2009 12:00:00 AM 60.0000 4 Hotel4 12/22/2009 12:00:00 AM 55.0000 . . . 

I have to show the results as shown below:

 cDate Hotel1 Hotel2 Hotel3 Hotel4 12/22/2009 12:00:00 PM 15 25 60 55 12/22/2009 12:00:00 AM .. .. .. .. 12/22/2009 12:00:00 AM 12/22/2009 12:00:00 AM 

Answer 1

If you are using SQL Server 2005, you can use the Pivot statement.

See the MSDN article .

Here SQL-Server 2005 T-SQL does what you want to do:

 DECLARE @listCol VARCHAR(2000) DECLARE @query VARCHAR(4000) SELECT @listCol = STUFF(( SELECT DISTINCT '],[' + Hotel FROM dbo.tblHotels ORDER BY '],[' + Hotel FOR XML PATH('') ), 1, 2, '') + ']' SET @query = 'SELECT * FROM (SELECT cDate,Hotel,price FROM dbo.tblHotels) p PIVOT (SUM(price) FOR Hotel IN (' +@listCol +')) AS pvt' EXECUTE (@query)