Solved

Rotate an MSSQl Table

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

707 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