Swaping Row to column

  • CREATE table Table1

    (

     Col1 varchar(100),

     Col2 varchar(100),

     Col3 varchar(100)

    )

    INSERT INTO Table1

    SELECT 'Data1','Data2','Data3'

    UNION ALL

    SELECT 'Data4','Data5','Data6' 

    UNION ALL

    SELECT 'Data7','Data8','Data9'

    SELECT * FROM Table1

     

    Thanks,

     

    Vivek

    /**A strong positive mental attitude will create more miracles than any wonder drug**/

  • ACTUAL
    Col1Col2Col3
    1Data1Data2Data3
    2Data4Data5Data6
    3Data7Data8Data9
    DESIRED
    Col1Col2Col3
    1Data1Data4Data7
    2Data2Data5Data8
    3Data3Data6Data9

    Regards,

    vivek

    /**A strong positive mental attitude will create more miracles than any wonder drug**/

  • Have you searched this site for information?  This is a frequently asked question and there are several EXCELLENT ways of doing this already posted....



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • Hunting has been done long back - 2 years before. If you don't mind, kindly provide the link if already posted.

    Well, hunt me if you can..?

    Vivek - HUB of Hunting Information.

    /**A strong positive mental attitude will create more miracles than any wonder drug**/

  • Go to the search page and type in "Cross Tab".  You will get more hits than I could post... 

    I wasn't born stupid - I had to study.

  • You can test the following script

    drop table table1

    drop table tableTmp

    drop table table2

    CREATE table Table1

    (

     Col1 varchar(100),

     Col2 varchar(100),

     Col3 varchar(100)

    )

    insert table1 values ('Data1','Data2','Data3')

    insert table1 values ('Data4','Data5','Data6')

    insert table1 values ('Data7','Data8','Data9')

    declare @nRow int

    select @nRow = count(*) from Table1

    declare @nCol int

    select @nCol = max(colid) from syscolumns where id = object_id('table1')

    print @nRow

    declare @ddl varchar(8000)

    set @ddl = 'CREATE table TableTmp(Col varchar(100), RowNum int identity(1,1))

         CREATE table table2(RowNum int identity(1,1),'

    declare @loop int

    set @loop = 1

    while @loop <= @nRow

    begin

     set @ddl = @ddl + 'col' + convert(varchar(3), @loop) + ' varchar(100),'

     set @loop = @loop + 1

    end

    set @ddl = @ddl + ')'

    set @ddl = replace(@ddl,',)',')')

    exec(@ddl)

    Declare @loopIn int

    Declare @IntToChar varchar(3)

    Declare @IntToCharIn varchar(3) 

    set @loop = 1

    while @loop <= @nCol

    begin

     set @IntToChar = convert(varchar(3), @loop)

     --exec('select col'+ @IntToChar + ' from Table1')

     exec('insert tableTmp(Col) select col'+ @IntToChar + ' from Table1')

     --exec('select * from tableTmp')

     set @loopIn = 1

     while @loopIn <= @nRow

     begin

      set @IntToCharIn = convert(varchar(3), @loopIn)

      if @loopIn = 1

       exec('insert table2(col' + @IntToCharIn + ') select col from tableTmp where rownum = ' + @IntToCharIn)

      else

       exec('update table2 set col' + @IntToCharIn + ' = b.col from table2 a , tableTmp b where a.rownum = ' + @IntToChar + ' and b.rownum = ' + @IntToCharIn)

      set @loopIn = @loopIn + 1

     end

     exec('truncate table tableTmp')

     set @loop = @loop + 1

    end

    alter table Table2 drop column rownum

    select * from Table1

    select * from table2

  • I give you full 100 out of 100. Thank you.

     

    Vivek

    /**A strong positive mental attitude will create more miracles than any wonder drug**/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply