sql pivot 例子（转）

转自： http://www.tsqltutorials.com/pivot.php

CREATE TABLE #temp123

(

Country varchar(15),

Variable varchar(20),

VaribleValue integer

)



INSERT INTO #temp123 VALUES ('North America','Sales',2000000)

INSERT INTO #temp123 VALUES ('North America','Expenses',1250000)

INSERT INTO #temp123 VALUES ('North America','Taxes',250000)

INSERT INTO #temp123 VALUES ('North America','Profit',500000)





INSERT INTO #temp123 VALUES ('Europe','Sales',2500000)

INSERT INTO #temp123 VALUES ('Europe','Expenses',1250000)

INSERT INTO #temp123 VALUES ('Europe','Taxes',500000)

INSERT INTO #temp123 VALUES ('Europe','Profit',750000)





INSERT INTO #temp123 VALUES ('South America','Sales',500000)

INSERT INTO #temp123 VALUES ('South America','Expenses',250000)



INSERT INTO #temp123 VALUES ('Asia','Sales',800000)

INSERT INTO #temp123 VALUES ('Asia','Expenses',350000)

INSERT INTO #temp123 VALUES ('Asia','Taxes',100000)





/** Show original table **/

SELECT * FROM  #temp123





/** Create crosstab using PIVOT **/

SELECT *

FROM #temp123

PIVOT

 (

 SUM(VaribleValue)

 FOR [Variable]

 IN ([Sales],[Expenses],[Taxes],[Profit])

 )

 AS p





DROP TABLE #temp123