Solved

How to rotate columns into rows?

Posted on 2004-10-22
13,824 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
Question by:qinyan
    9 Comments
     

    Expert Comment

    by:peapodd
    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
    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
    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
     

    Author Comment

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

    Expert Comment

    by:kselvia
    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
    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:
    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
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    934 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