[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


How to rotate columns into rows?

Posted on 2004-10-22
Medium Priority
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!
Question by:qinyan
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

Expert Comment

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.

Author Comment

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!

Expert Comment

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

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'
           CASE a
                 WHEN 1 THEN table1.date1
                 WHEN 2 THEN table1.date2
                 WHEN 156 THEN table1.date156
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

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

Expert Comment

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


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.

Expert Comment

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'

Accepted Solution

ala_frosty earned 1500 total points
ID: 12388178

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

Expert Comment

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 '
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
      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

CLOSE Product_cursor
DEALLOCATE Product_cursor

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


Expert Comment

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

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


Featured Post

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.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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 …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

656 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