Solved

Rotate an MSSQl Table

Posted on 1998-11-10
3
558 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 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
T-SQL: Only Wanting One Record 8 59
EF5 How do I stop pre-compiled views? 8 50
Format Output of Select Statement 2 36
Suppress if value zero or NULL in crystal report 2 39
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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