How to rotate columns into rows?

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!
qinyanAsked:
Who is Participating?
 
ala_frostyConnect With a Mentor Commented:
oops

while @i > 0 begin
  print 'insert table2 select product, date' + cast(@i as varchar(3)) + ' from table1'
  set @i = @i - 1
end
0
 
peapoddCommented:
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
 
qinyanAuthor Commented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
izblankCommented:
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
 
qinyanAuthor Commented:
Thanks for the reply! I'll take this too even though it's still a lot of typing.
0
 
Ken SelviaRetiredCommented:
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
 
ala_frostyCommented:
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
 
dsreekanthCommented:
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
 
progressivelearningCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.