Transposing a table with SQL

Question

Hi there,

I have a SQL table like so

PV      Area    CouterParty

851     M010    Name1

561     M011    Name2

869     M012    Name3
...

And I need to transpost it, using T-SQL (not reporting services or anything else), so that it looks like this:

CounterParty M010 M011 M012 .... 

Name1        851 

Name2             561 

Name2                  869 

The thing is that I do not know how many Area codes I will have, so there could be any number of columns really.

Any ideas how I can do this? Thanks a milion

Answer 1

You could do this via a cursor loop and a temporary table.

Select all of you area codes, run the cursor for each fetched row insert rows into your temporary table as necessary close your cursor select from your temporary table

Answer 2

You will have to do a Dynamic CrossTab, check these articles:

• SQLServer: Dynamic Cross-Tab
• Dynamic Cross-Tabs/Pivot Tables
• Crosstab Pivot-table Workbench
• SQL Dynamic CrossTab Stored Procedure