...

View Full Version : Troubles with SQL - pivot?



bostjank
07-07-2007, 05:03 PM
Hello,

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,
Gender,
VisitorDate As [Date of Visit],
Sum(VisitorNumber) As [Number of Visitors],
(Sum(VisitorNumber)/365) As [Some other Number]
FROM Visitors
GROUP BY Country,
Gender,
VisitorDate
ORDER BY Country,
VisitorDate


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,
Bostjan



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum