CODEWITHSUNDEEP
sqlsql-servert-sql
DLL
DLL

Reputation:

Transposing a table with SQL

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

Upvotes: 1

Views: 934

Answers (3)

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12555

Use pivot Function if you use SQL Server 2005 or latest

SELECT 
    CouterParty ,
    [M010] ,
    [M011],
    [M012]
FROM    ( SELECT    
            PV ,Area    ,CouterParty
          FROM      TablesName AS T
        ) AS T1Temp PIVOT ( Sum(PV) FOR T1Temp.CouterParty IN ( [M010],
                                                          [M011],
                                                          [M012] ) )  As PivotTable

Upvotes: 0

Christian C. Salvadó
Christian C. Salvadó

Reputation: 827198

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

Upvotes: 4

TheTXI
TheTXI

Reputation: 37875

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

Upvotes: 0

Related Questions