View Full Version : Troubles with SQL - pivot?

07-07-2007, 05:03 PM

I have troubles constructing SQL query and hope some of you could help - I'm using MS SQL 2005. I have a table with data on number of visitors by gender on specific town in a specific country on a specific day.

CREATE TABLE [dbo].[Visitors](
[Country] [nvarchar](50) NULL,
[Town] [nvarchar](50) NULL,
[Gender] [char](1) NULL,
[VisitorNumber] [int] NULL,
[VisitorDAte] [datetime] NULL
I need to get the number of visitors and also some other calclulations based on that number grouped by country, gender and date - this is not a problem and I use the following query to get the results

SELECT Country,
VisitorDate As [Date of Visit],
Sum(VisitorNumber) As [Number of Visitors],
(Sum(VisitorNumber)/365) As [Some other Number]
FROM Visitors
GROUP BY Country,
ORDER BY Country,

The problem that I cannot solve is that the result must presented in the some sort of pivot view as shown in the image attached to this post. I have tried to use the PIVOT command, but had no success.

I would be very grateful if you could help me solve this problem - it has to be solved with SQL.

Thans a lot,