T-SQL 行列互换

对于一个简单的表, 两行两列(行号暂且不算). 要对它进行行列互换, 怎么做?

先准备测试数据:

CREATE TABLE TempSum(
    n INT,
    gender VARCHAR(10),
    total INT
);

INSERT INTO TempSum(n, gender, total) VALUES
(1, 'male', 23), (2, 'female', 45);

SELECT * FROM TempSum;

n    gender total
---- ------ -----
   1 male      23
   2 female    45

 

看到行列互换, 大家首先想到的大概是PIVOT:

SELECT n, male, female
FROM TempSum
pivot(max(total) FOR gender
    IN (male, female)) AS P;

n    male female
---- ---- ------
   1   23   NULL
   2 NULL     45

 

或者, n列也可以去掉的:

SELECT
    -- n, --uncomment to show n
    max(CASE WHEN gender='male' THEN total END) AS male,
    max(CASE WHEN gender='female' THEN total END) AS female
FROM TempSum
GROUP BY n;

male female
---- ------
  23   NULL
NULL     45

 

如果, 我不想要NULL, 要显示成

male female
23   45    

的样式. 或者, 如果TempSum表中根本没有n列. 怎么办?

 

思路:

Step1, 把male和female放到一行

Step2, 把23和45放到一行, 要注意它们和gender的对应顺序

Step3, 把Step1和Step2的两行作个UNION. OK!

 

实现:

Step1, gender可以认为是已知的, hard code就可以的:

SELECT 'male', 'female' 

Step2, male和female的total本不在同一行, 可以做个自连接, 选出来一行让它左边对应male的total, 右边对应female的total:

SELECT convert(VARCHAR(10), a.total), convert(VARCHAR(10), b.total)
FROM TempSum a JOIN TempSum b ON a.gender<>b.gender
WHERE a.gender='male' --AND b.gender='female'

Step3, 用UNION ALL就可以的, 就不多解释了:

SELECT 'male', 'female' 
UNION ALL
SELECT convert(VARCHAR(10), a.total), convert(VARCHAR(10), b.total)
FROM TempSum a JOIN TempSum b ON a.gender<>b.gender
WHERE a.gender='male' --AND b.gender='female';

-- results
No Column Name No Column Name
-------------- --------------
male           female        
23             45            

 

看到No Column Name, 把它也去掉吧. 其实, 走到Step2的时候, 大概就可以想到了:

SELECT convert(VARCHAR(10), a.total) AS male, convert(VARCHAR(10), b.total) AS female
FROM TempSum a JOIN TempSum b ON a.gender<>b.gender
WHERE a.gender='male'

-- results
male female
---- ------
23   45    

 

到这里, 我们已经抛弃了最初提到的n列, 屏蔽了NULL. 我们可以把列名gender、total加回来:

SELECT 'total' AS gender, convert(VARCHAR(10), a.total) AS male, convert(VARCHAR(10), b.total) AS female
FROM TempSum a JOIN TempSum b ON a.gender<>b.gender
WHERE a.gender='male'

 

最终, 我们从

gender total
------ -----
male      23
female    45

得到了

gender male female
------ ---- ------
total  23   45