?
Solved

Rotate an MSSQl Table

Posted on 1998-11-10
3
Medium Priority
?
560 Views
Last Modified: 2008-03-04
How do I rotate row values into columns ? The column values are not always known so the solution must be dynamic. for example:
cust_name char(25) contains 70+ values How to create a new table dynamically via a stored proc / cursor ??/
 
0
Comment
Question by:is9tech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 9

Expert Comment

by:david_levine
ID: 1091199
Are you saying you have 70 rows in your table? what do you want to become the row and data to display under each?

And... what happens when you have 200 customers?

I think you need to better explain what you are really looking to do.

David
0
 
LVL 7

Accepted Solution

by:
tchalkov earned 300 total points
ID: 1091200
This TSQL statment shwos the basic idea of creating the structure of the dynamic table. Once you have the table created you can easy put the necessary records into it.
This example takes the column names from column 'city' in table addresses and adds a column to temp1 for every city which appears in addresses. However you must know that there is a limit of the number of columns a table can contain - <=250

declare @colname varchar(255)
declare temp2 cursor for select city from addresses
open temp2
fetch next from temp2 into @colname
while (@@fetch_status=0)
begin
   exec ('alter table temp1 add '+@colname+' char(20) NULL')
   fetch next from temp2 into @colname
end
close temp2
deallocate temp2
0
 
LVL 2

Expert Comment

by:aliciaam
ID: 1091201
Cursors should be avoided if possible.
Here is how you can do it:

 
   Year   Quarter       Amount
   -------------------------------
 
   1995      1          125,000.90
   1995      2          136,000.75
   1995      3          212,000.34
   1995      4          328,000.82
   1996      3          728,000.35
   1996      2          422,000.13
   1996      1          328,000.82
 
Now suppose you want to rotate the table, to see the data in the following
format:
 
   YEAR        Q1              Q2              Q3              Q4
   -------------------------------------------------------------------
 
   1995     125,000.90      136,000.75      212,000.34      328,000.82
   1996     328,000.82      422,000.13      728,000.35            0.00
 
The following is the query you would use to rotate the table:
 
SELECT YEAR,
       Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =
Q.YEAR),0),
       Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =
Q.YEAR),0),
       Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =
Q.YEAR),0),
       Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =
Q.YEAR),0)
     FROM QTRSALES Q
     GROUP BY YEAR
 
For very large tables, the following query should be faster:
 
year=q.year,
SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1,
SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2,
SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3,
SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4
FROM qtrsales q
GROUP BY year
 

0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 …
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

765 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