Solved

PIVOT TABLE - SQL Server 2000

Posted on 2007-04-11
3
261 Views
Last Modified: 2010-05-18
Hello.  I am trying to develop an ASP.NET app that displays linear data as a pivot table, and I have no idea how to go about it. My table is set up like this:
VESSEL
VOYAGE
PORT
ARRIVAL
I want this to display in a table like the following:
VESSEL   VOYAGE   PORT1   PORT2   PORT3   (Ports are different)
vsl1          voy1          date1      date2    date3
vsl2          voy2          date1      date2    date3
vsl3          voy3          date1      date2    date3

Can somebody help me with this? In Access, this is pretty easy, but how do I do this in SQL Server 2000?
0
Comment
Question by:adwooley2
  • 2
3 Comments
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18887956
here's a nice explanation of how to achieve this, using CASE:

Creating cross tab queries and pivot tables in SQL
http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/
0
 
LVL 18

Accepted Solution

by:
Sham Haque earned 500 total points
ID: 18887975
your example:

SELECT vessel
            ,voyage
            ,port1 = CASE PORT WHEN 'port1' then arrival else null end
            ,port2  = CASE PORT WHEN 'port2' then arrival else null end
            ,port3 = CASE PORT when 'port3' then arrival else null end
FROM voyages
0
 

Author Comment

by:adwooley2
ID: 18888289
Appears easy.  I'll give it a try.  Thanks for your quick response.

Regards,
Alan
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question