问题:

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

回答1:

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

回答2:

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

回答3:

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