Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    The sunny side of the Alps
    Posts
    230
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lightbulb Troubles with SQL - pivot?

    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.
    Code:
    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

    Code:
    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
    Attached Thumbnails Attached Thumbnails Troubles with SQL - pivot?-table_presentation.jpg  


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •