Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to Pivot a table

Posted on 2011-10-24
Medium Priority
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 40

Accepted Solution

lcohan earned 2000 total points
ID: 37020778
Please see examples at link below about how to do it with cross-tab or pivot

LVL 70

Expert Comment

by:Scott Pletcher
ID: 37025771
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

572 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