Solved

Rotate an MSSQl Table

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dynamics crm 2011 8 44
C# Application Local DB Connection String 23 58
How to query date ranges with SQL 6 24
SQL Date Retrival 7 27
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Viewers will learn how the fundamental information of how to create a table.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now