?
Solved

How to rotate columns into rows?

Posted on 2004-10-22
9
Medium Priority
?
13,834 Views
Last Modified: 2013-11-30
I am trying to rotate 186 columns into rows. For eg, what I have is like this:

product    date1    date2     date3
A              100      100          200
B              200      200         150

And I want to make it look like this:

A     date1      100
A     date 2      100
A     date3       200
B     date1       200
B     date2       200
B     date3       150

Can you do this in EXCEL? What I have is in a spreadsheet which I need to use DTS to import into MS SQL Server as a table to perform query on. Can simple query handle this? Or I have to write sp?

Thanks a lot!
0
Comment
Question by:qinyan
9 Comments
 

Expert Comment

by:peapodd
ID: 12383931
If I was doing it in SQL, I'd do it something like this:
 
insert table2
select product, date 1 from table1
 
insert table2
select product, date 2 from table1
 
insert table2
select product, date3 from table1
 
That would normalize your data for you, which is basically what you're trying to do.  Then you could select from table2 for the final result set.

I'm not sure if a pivot table in MS Excel would help you or not.
0
 

Author Comment

by:qinyan
ID: 12384922
Thanks for the reply! I believe it works. But I have 156 columns in the table so I have to change the column name 156 times so is there a quick way to do this like writing a stored procedure? Anyway if there is no better way I think I'll have to do like this. Thanks again!
0
 
LVL 6

Expert Comment

by:izblank
ID: 12385266
If the table is big, 156 table scans can take a while.  Another solution could be having an auxillary table:

CREATE TABLE Numbers
(
a int
);
insert into Numbers values(1);
insert into Numbers values(2);
insert into Numbers values(3);
...................
insert into Numbers values(156);

Then you do it all in one select:

INSERT table2
SELECT product,
           CASE a
                 WHEN 1 THEN 'date1'
                 WHEN 2 THEN 'date2'
                 ..............................
                 WHEN 156 THEN 'date156'
           END,
           CASE a
                 WHEN 1 THEN table1.date1
                 WHEN 2 THEN table1.date2
                 ..............................
                 WHEN 156 THEN table1.date156
           END
FROM table1, Numbers
WHERE Numbers.a  BETWEEN 1 AND 156


You can even run a piece of T-SQL to create the SQL above for you
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:qinyan
ID: 12385534
Thanks for the reply! I'll take this too even though it's still a lot of typing.
0
 
LVL 12

Expert Comment

by:Ken Selvia
ID: 12386435
To shorten your typing chore, this will create the table Numbers

select top 156 a = identity(int,1,1) into Numbers from sysobjects s1, sysobjects s1, sysobjects s1

And run this

select 'WHEN '+cast(a as varchar)+' THEN '''date' +cast(a as varchar)+ ''''' from Numbers

and

select 'WHEN '+cast(a as varchar)+' THEN table1.date' +cast(a as varchar) from Numbers

to generate all of the WHEN statements that you can cut and paste into your code.
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 12388176
Here's a query that will write your 186 queries for you:

declare @v varchar(8000)
declare @i int
set @i = 186

while @i > 0 begin
  print 'insert table2 select product, date' + cast(@i as varchar(3)) + ' from table1'
end
0
 
LVL 7

Accepted Solution

by:
ala_frosty earned 1500 total points
ID: 12388178
oops

while @i > 0 begin
  print 'insert table2 select product, date' + cast(@i as varchar(3)) + ' from table1'
  set @i = @i - 1
end
0
 

Expert Comment

by:dsreekanth
ID: 12420507
Please try the following stored procedure,

For this I assumed that Date1, Date2, Date3 .... Date156 No Other Column(s) at the end or middle of the table.

If you have Other columns Change this Query in the SP for filtering those unwanted Columns
'select a.name from syscolumns a join sysobjects b on a.id=b.id where b.name='Product' and a.colorder > 1 Order By a.colorder'

call the SP : EXEC PrepareTable

-------------

CREATE Procedure PrepareTable AS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sequence_tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Sequence_tbl]

CREATE TABLE Sequence_tbl ( SNo Int )

DECLARE @sqlString VarChar(8000)
DECLARE @sqlStringColumn VarChar(8000)
DECLARE @sqlStringValues VarChar(8000)
DECLARE @colName VarChar(8000)
DECLARE @tmpSeq Int
SET @sqlString = 'SELECT Product , '
SET @sqlStringColumn = 'CASE SNo '
SET @sqlStringValues = 'CASE SNo '
DECLARE Product_cursor CURSOR FOR
select a.name from syscolumns a join sysobjects b on a.id=b.id where b.name='Product' and a.colorder > 1 Order By a.colorder


OPEN Product_cursor
FETCH NEXT FROM Product_cursor INTO @colName
SET @tmpSeq = 1
WHILE @@FETCH_STATUS = 0
BEGIN
      Insert Into Sequence_tbl Values ( @tmpSeq )
      SET @sqlStringColumn = @sqlStringColumn + ' ' + ' WHEN ' + CAST(@tmpSeq As Varchar) + ' THEN ''' + CAST(@colName As Varchar) + ''' '
      SET @sqlStringValues = @sqlStringValues + ' ' + ' WHEN ' + CAST(@tmpSeq As Varchar) + ' THEN ' + CAST(@colName As Varchar) + ' '
      FETCH NEXT FROM Product_cursor INTO @colName
      SET @tmpSeq = @tmpSeq + 1
END

CLOSE Product_cursor
DEALLOCATE Product_cursor

SET @sqlString = @sqlString + @sqlStringColumn + ' END As DateColumn, ' + @sqlStringValues + ' END As DateValue FROM Product , Sequence_tbl '
EXEC (@sqlString)
GO

0
 

Expert Comment

by:progressivelearning
ID: 21224517
Hi. I found an easy way to rotate rows into columns. Select the data you want to turn into columns. Then click edit, copy. Now go to the column and select the same number of cells down. Then click paste special, and click transpose then ok.

Here's an example:

1 2 3 <- Copy this
Select 3 down in a column and paste, special, transpose
and you get
1
2
3

You can do each row this way to change your whole spreadsheet.

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

578 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