?
Solved

Convert Columns To Rows

Posted on 2007-10-09
5
Medium Priority
?
1,503 Views
Last Modified: 2009-03-05
I have a table with 5 columns and 5 rows as below:

Comany      HeadcountStart      Starters      Leavers            HeatcountEnd
------      --------------                            --------      -------            ------------
AAA      500            10      5            505
BBB      180            5      0            185
CCC      225            15      5            235
DDD      50            3      0            53
EEE      200            0      5            195

I need to convert the data in the columns into rows as follows:

Company            AAA      BBB      CCC      DDD      EEE
HeadcountStart      500      180      225      50      200
Starters                           10      5      15      3      0
Leavers            5      0      5      0      5
HeadcountEnd      505      185      235      53      195

The number of rows and columns are static.

I'm using SQL 2000.

Thanks for help.
0
Comment
Question by:mrj1976
3 Comments
 
LVL 27

Accepted Solution

by:
ptjcb earned 1000 total points
ID: 20040000
http://sqlserver2000.databases.aspfaq.com/how-do-i-create-a-cross-tab-or-pivot-query.html

http://snippets.dzone.com/posts/show/2000

There are many links to walk you through how to do pivot tables using SQL 2000 t-sql.
0
 
LVL 23

Assisted Solution

by:Ashish Patel
Ashish Patel earned 1000 total points
ID: 20040002
If the data is static then you can do this.

Select 'HeadCountStart' As Company, 500 As AAA, 180 As BBB, 225 as CCC, 50 As DDD, 200 AS EEE
Union
Select 'Starters' As Company, 10 As AAA, 5 As BBB, 15 as CCC, 3 As DDD, 0 AS EEE
Union
.....
0
 
LVL 6

Expert Comment

by:openshac
ID: 23809116
Here's a copy of the accepted solution which said was Force Closed:

ptjcb:
http://sqlserver2000.databases.aspfaq.com/how-do-i-create-a-cross-tab-or-pivot-query.html

http://snippets.dzone.com/posts/show/2000

There are many links to walk you through how to do pivot tables using SQL 2000 t-sql.

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

840 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