一個簡單的行轉列,用pivot應該怎麼寫啊?

[IDispatch]

表
name, f1, f2
a aaa 1
b bbb 2
a aaa 2
b ccc 1

要轉成
name,1,2
a,aaa,aaa
b,ccc,bbb

[黄_瓜]

给你贴个例子看看吧
SQL Server 2005之PIVOT/UNPIVOT行列转换
SQL Server2005引入了很多迎合开发者口味的新特性，虽然改动不大，却大大了减少了开发者的工作量，这种替用户考虑的开发思路，值得称赞。

在SQL Server2000中，要实现行列转换，需要综合利用聚合函数和动态SQL，实现起来需要一定的技巧，所以在CSDN的SQL讨论区里可以看到大量询问行列转换如何实现的问题。到了2005中，使用新引进的关键字PIVOT/UNPIVOT，可以轻松实现行列转换的需求。

好像Oracle11g也准备引入PIVOT/UNPIVOT特性，对于Oracle开发来说，It's a good news。

本文通过两个简单的例子展示PIVOT/UNPIVOT的用法。详细的语法请参考联机帮助。



PIVOT

创建测试表，插入测试数据

create table test(id int,name varchar(20),quarter int,profile int) 
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)

select * from test
id name quarter profile
----------- -------------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500

(8 row(s) affected)

利用PIVOT将个季度的利润转成横向显示：

select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt

id name 一季度 二季度 三季度 四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500

[黄_瓜]

------------------------------------------------
-----------------SQL 版本--------------------- 
--  Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
---------------2009-09-14 01:01:34-----------------------

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(1),[f1] varchar(3),[f2] int)
insert [tb]
select 'a','aaa',1 union all
select 'b','bbb',2 union all
select 'a','aaa',2 union all
select 'b','ccc',1

select [name], [1],[2] from [tb]
pivot
(
max([f1])
for [f2] in
([1],[2])
)
as pvt
-----------------结果-------------------
/*name 1    2
---- ---- ----
a    aaa  aaa
b    ccc  bbb

(2 行受影响)

*/

[htl258_Tony]

--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb]([name] NVARCHAR(10),[f1] NVARCHAR(10),[f2] INT)
INSERT [tb]
SELECT 'a','aaa',1 UNION ALL
SELECT 'b','bbb',2 UNION ALL
SELECT 'a','aaa',2 UNION ALL
SELECT 'b','ccc',1
GO
--SELECT * FROM [tb]

-->SQL查询如下:
SELECT *
FROM tb
	PIVOT(MAX(f1) FOR f2 IN([1],[2]))b
/*
name       1          2
---------- ---------- ----------
a          aaa        aaa
b          ccc        bbb
*/

[xiequan2]

ls正解，我就不写了


http://blog.csdn.net/zhanghongju/archive/2006/06/02/769445.aspx

pivot的执行过程!

[水族杰纶]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(1),[f1] varchar(3),[f2] int)
insert [tb]
select 'a','aaa',1 union all
select 'b','bbb',2 union all
select 'a','aaa',2 union all
select 'b','ccc',1
select [name],
       max([f1])[1],
       min([f1])[2]
from tb group by [name]
/*
name 1    2
---- ---- ----
a    aaa  aaa
b    ccc  bbb

(2 個資料列受到影響)
*/

[guguda2008]

表述不清，不猜了

[Zoezs]

[Quote=引用 4 楼 htl258 的回复:]
SQL code--> 生成测试数据表:tbIFNOTOBJECT_ID('[tb]')ISNULLDROPTABLE[tb]GOCREATETABLE[tb]([name]NVARCHAR(10),[f1]NVARCHAR(10),[f2]INT)INSERT[tb]SELECT'a','aaa',1UNIONALLSELECT'b','bbb',2UNIONALLSELECT'a','aaa'¡­
[/Quote]
楼上正解。

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-14 09:11:27
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([name] varchar(1),[f1] varchar(3),[f2] int)
insert [tb]
select 'a','aaa',1 union all
select 'b','bbb',2 union all
select 'a','aaa',2 union all
select 'b','ccc',1
--------------开始查询--------------------------
select 
  [name], [1],[2] 
from 
  [tb]
pivot
(
  max([f1])
  for [f2] 
  in
([1],[2])
)
as f

----------------结果----------------------------
/* name 1    2
---- ---- ----
a    aaa  aaa
b    ccc  bbb

(2 行受影响)

*/

[--小F--]

联机丛书上的例子
USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

[lihan6415151528]

http://www.cnblogs.com/yank/archive/2008/09/10/1287556.html

[黄_瓜]

转为一列？

[ADVANTECH_CYZ]

我就接分了。