How to Pivot a table

Posted on 2011-10-24
Last Modified: 2012-06-22

I am wondering if it's possible to transform/pivot a table like the following.  Let's say I have a table that looks something like this:

rowId             Col1
1                    Car
2                    Bike
3                    Train

I would like to change the format to something like the folloiwng

1                      2                      3
Car                  Bike                  Train

Even better if I could rename column 1,2,3 to something more meaningful on the fly.

Question by:yechan
    LVL 39

    Accepted Solution

    Please see examples at link below about how to do it with cross-tab or pivot
    LVL 68

    Expert Comment

    If the "rowId" (controlling) column has fixed values, you can do this:

        MAX(CASE WHEN rowId = 1 THEN Col1 END) AS [Some name],
        MAX(CASE WHEN rowId = 2 THEN Col1 END) AS [Some other name],
        MAX(CASE WHEN rowId = 3 THEN Col1 END) AS [Yet a third name],
    FROM tablename


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    755 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

    23 Experts available now in Live!

    Get 1:1 Help Now