Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

行变列

    注：要变列的行单元对应的数据须唯一，故在下面的子查询中先对目标行单元进行了分组，以保证唯一性。
    1，用case指定条件
    SELECT f.UserName,
    SUM(CASE CONVERT(VARCHAR(10),b.OrderDate,120) WHEN '2011-03-10' THEN b.Amount ELSE 0  END) AS '2011-03-10',
    SUM(CASE CONVERT(VARCHAR(10),b.OrderDate,120) WHEN '2011-03-11' THEN b.Amount  ELSE 0 END) AS '2011-03-11',
    SUM(CASE CONVERT(VARCHAR(10),b.OrderDate,120) WHEN '2011-03-12' THEN b.Amount  ELSE 0 END) AS '2011-03-12' 
    FROM bdAchievement a(NOLOCK)
    JOIN bdOrder b(NOLOCK) ON a.OrderNo = b.OrderNo
    JOIN frmuser f ON a.SalesStaff = f.Account
    WHERE a.RootDepartmentID  = 184 AND b.OrderDate >='2011-03-10'
    GROUP BY F.UserName

    2，用SQL SERVER2005提供的关键词PIVOT来执行
    行变列：将多行的值合并放到列上去，这就需要一个聚合函数，此外要指定拆分成行（列一行）的列并且此列按如何规则分配到行上去
    SELECT * FROM (
    SELECT f.UserName,CONVERT(VARCHAR(10),OrderDate,120) orderdate,b.Amount 
    FROM bdAchievement a(NOLOCK)
    JOIN bdOrder b(NOLOCK) ON a.OrderNo = b.OrderNo
    JOIN frmuser f ON a.SalesStaff = f.Account
    WHERE a.RootDepartmentID  = 3 AND b.OrderDate >='2010-02-10') O
    PIVOT (SUM(amount) FOR orderdate IN([2011-03-10],[2011-03-11],[2011-03-12],[2011-03-13])) AS c
    将列订单日期转换到行上去，分成多个列，规则是[2011-03-10],[2011-03-11],[2011-03-12],[2011-03-13])，并对要求的amount求和

    同样列变行为：UNPIVOT
注：应用限制，列拆分规则是有限的几个，即行名是可指定的，若行名是未知数，那查出的数据将不知放到哪一列，故这样的行转列是则不可执行

注: PIVOT 与CASE 方法的区别：PIVOT简洁，但只能将一个列的列值转为列，没有CASE灵活


3，将列直接转成行
现有一客户电话表，客户电话最多4个，要求以行形式显示出来

CREATE TABLE tmpTable(CustomerID INT,Tel VARCHAR(20))

INSERT INTO tmpTable(CustomerID,Tel) VALUES(222,'13587654321')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(222,'13587654322')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(222,'13587654323')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(222,'13587654324')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(333,'13587654331')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(333,'13587654332')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(444,'13587654341')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(444,'13587654332')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(444,'13587654332')

SELECT * FROM (
SELECT customerid,tel, ROW_NUMBER () OVER ( PARTITION BY CustomerID ORDER BY  tel DESC )r
FROM tmpTable tt)m
PIVOT (max(tel) FOR r IN ([1],[2],[3],[4]) ) AS t