Transposing the data in the table

  • I need to transpose the data in the table

    The table is something like

    Seq_no Row_On_Dom Dow_On_Dom

    12172 34

    24545 56

    I want this to be like

    Seq_no 1 2

    Row_On_Dom 2172 4545

    Dow_On_Dom 34 56

    Can anybody suggest how should I go in this case.

    I do use SQL server 2000.

  • You can find the best info on how to do this in this article[/url]

    HTH

    Dave J


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • this is a better option... this works well for 3 columns... was developed by my friend.... good one...

    create the below table

    PIVOTTABLE

    Col1varchar(100)

    col2varchar(100)

    Col3varchar(100)

    create Proc Usp_PivotRep

    as

    declare @col varchar(100) -- Variable to get the second column while looping to build the query

    declare @Sql1 varchar(7000) -- Variable to build the Sum of Case when Query

    declare @Sql2 varchar(1000) -- Variable to build the heading Row

    declare @sqlfull varchar(8000)

    declare @sql varchar(8000)

    declare colcur cursor for select distinct col2 from PivotTable -- Cursor to get the Distinct Heading

    set @Sql2='select ''header'' as header ' --Building the heading row ,''total'' as total,''sum'' as sum

    set @sql='select col1 as header' --Building the main query ,cast(count(*) as varchar(100)) as Total,cast(sum(cast(col3 as int)) as varchar(100)) Sum

    open colcur

    fetch next from colcur into @col

    while @@Fetch_status = 0

    begin

    if @Col<>''

    begin

    set @Sql2= @Sql2 + ','''+@col+''' as ['+@col + ']' --Building the heading query

    set @sql= @sql + ', cast( sum ( Case when col2='''+@col+'''then col3 else 0 end ) as varchar(100)) as ['+@col +']' --Building the Case when query

    end

    fetch next from colcur into @col

    end

    CLOSE colcur

    deallocate colcur

    set @sql= @sql + ' from PivotTable group by col1 '

    --print @sql

    --print @Sql2

    set @sqlfull= @sql --Concatinating the Heading query and the Main Query with union all @sql2 +' union all ' +

    exec(@sqlfull)

    Jus fill the values in the pivottable table and run de proc... works real fast..

    [font="Times New Roman"]For better assistance in answering your questions
    Click Here[/url][/font]

  • GOD (8/29/2008)


    this is a better option... this works well for 3 columns... was developed by my friend.... good one...

    Jus fill the values in the pivottable table and run de proc... works real fast..

    Jus run de proc and get de wrong results...

    header 2172 4545

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

    1 34 0

    2 0 56

    A far better option than getting the wrong results would be to follow the advice given by David and read Jeff Moden's recent excellent article about cross tabs. It will have you up and running in no time.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 4 posts - 1 through 3 (of 3 total)

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