行列互换 pivot和unpivot

1.列转行

 ---测试表

CREATE TABLE [dbo].[te]
(
[id] [int] NULL,
[name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL,
[quarter] [int] NULL,
[profile] [int] NULL
) ON [PRIMARY]
GO



SELECT  *
FROM    te 
 
--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

-----sql2005以上 pivot
SELECT  *
FROM    dbo.te PIVOT ( SUM(profile) FOR quarter IN ( [1], [2], [3], [4] ) ) as p  ---for 前面是你需要转换的列 ---for后面为你的分组列

--1    a    1000    2000    4000    5000
--2    b    3000    3500    4200    5500


--sql 2000 版本
SELECT  id,NAME,
        SUM(CASE WHEN quarter = 1 THEN profile ELSE 0
            END) AS Q1,
        SUM(CASE WHEN quarter = 2 THEN profile ELSE 0
            END) AS Q2,
        SUM(CASE WHEN quarter = 3 THEN profile ELSE 0
            END) AS Q3,
        SUM(CASE WHEN quarter =4 THEN profile ELSE 0
            END) AS Q4
FROM    te
GROUP BY id,name


 -----行转列

 

---测试表
CREATE TABLE [dbo].[te2]
(
[id] [int] NULL,
[name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL,
[Q1] [int] NULL,
[Q2] [int] NULL,
[Q3] [int] NULL,
[Q4] [int] NULL
) ON [PRIMARY]
GO

  
--1    a    1000    2000    4000    5000
--2    b    3000    3500    4200    5500
  
SELECT  *
FROM    dbo.te2


----unpivot
SELECT  id ,
        name ,
        t
FROM    ( SELECT    id ,
                    q1 ,
                    q2 ,
                    q3 ,
                    q4
          FROM      te2
        ) td UNPIVOT ( t FOR name IN ( q1, q2, q3, q4 ) ) AS u
 
--1    q1    1000
--1    q2    2000
--1    q3    4000
--1    q4    5000
--2    q1    3000
--2    q2    3500
--2    q3    4200
--2    q4    5500


 --UNPIVOT 的sql 2000 实现语句：
SELECT id,NAME,q1 AS T FROM dbo.te2 
UNION 

SELECT id,NAME,q2  FROM dbo.te2 
 UNION 

SELECT id,NAME,q3  FROM dbo.te2 
UNION 

SELECT id,NAME,q4  FROM dbo.te2