Solved

Rotate an MSSQl Table

Posted on 1998-11-10
3
549 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
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 100 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

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

Suggested Solutions

Title # Comments Views Activity
TSQL Challenge... 7 37
MS SQL: Return all results in a single row separated by commas 1 27
SQL Server / Update DB? 22 38
SQL Quer 4 22
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

830 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