Transposing rows to column in sql server

Posted on 2012-08-16
Last Modified: 2012-08-16
I need a help.

I have a table where the marks are getting inserted.

RollNo | PaperCode | Marks
A         | 1                |45
A         | 2                |26
A         | 4                |30
B         | 2                |98
B         | 3                |58

I want the result in a table in this way:

RollNo | 1   |2   |3   |4  
A         | 45 |26 | 0  |30
B         |   0  |98 |58 |0

Please help me how can I do like this.

Question by:sam000
    LVL 25

    Expert Comment

    LVL 25

    Accepted Solution

    Two methods:
    IF OBJECT_ID('tempdb..#tmp') IS NOT NULL 
          DROP TABLE #tmp
    create table #tmp (
        RollNo          char(1),
        PaperCode       int,
        Marks           int
    insert into #tmp values 
    ('A', 1, 45),('A', 2, 26),('A', 4, 30),('B', 2, 98),('B', 3, 58)
    -- using CASE statements
    select rollno, 
           sum(case PaperCode when 1 then Marks else 0 end) as [1],
           sum(case PaperCode when 2 then Marks else 0 end) as [2],
           sum(case PaperCode when 3 then Marks else 0 end) as [3],
           sum(case PaperCode when 4 then Marks else 0 end) as [4]
    from #tmp
    group by rollno
    -- using PIVOT
    select rollno, [1], [2], [3], [4]
    from #tmp
    pivot (sum(Marks) for Papercode in ([1], [2], [3], [4])) p

    Open in new window


    Author Comment


    Author Closing Comment

    nice reply

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    728 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