日期:2014-05-18  浏览次数:20452 次

行列互换
怎么能让查询结果中的行列互换

例如 select id,name,age,sex from person;
本来显示是 id name age sex
  01 xiao 21 男
    02 li  22 女
现在我想让 id 01 02
  name xiao li
  age 21 22
  sex 男 女

请问用SQL怎么实现?

------解决方案--------------------
CREATE TABLE T
(
ID VARCHAR(20),
Name VARCHAR(20),
Age INT,
Sex VARCHAR(20)
)

INSERT INTO T
SELECT '01','xiao',21,'男' UNION ALL
SELECT '02','li',22,'女'

DECLARE @sql VARCHAR(8000)
SET @sql = ''
SELECT @sql =@sql+ ',['+CASE WHEN PATINDEX('%[^0-9]%',COL1)>0 THEN RIGHT(COL1,2) ELSE COL1 END+'] = MIN(CASE WHEN COL1 = '''+CASE WHEN PATINDEX('%[^0-9]%',COL1)>0 THEN RIGHT(COL1,2) ELSE COL1 END+''' THEN col2 END)'
FROM
(
SELECT id=0,col1=id,col2=Name FROM t
UNION ALL
SELECT id=1,col1=id,CAST(AGE AS VARCHAR) FROM t
UNION ALL
SELECT id=2,col1=id,SEX FROM t
UNION ALL
SELECT 0,'00id','name'
UNION ALL
SELECT 1,'00id','age' 
UNION ALL
SELECT 2,'00id','sex'
) A GROUP BY COL1

SET @sql = STUFF(@sql,1,1,'')

EXEC ('SELECT '+@sql+'
FROM
(
SELECT id=0,col1=id,col2=Name FROM t
UNION ALL
SELECT id=1,col1=id,CAST(AGE AS VARCHAR) FROM t
UNION ALL
SELECT id=2,col1=id,SEX FROM t
UNION ALL
SELECT 0,''id'',''name''
UNION ALL
SELECT 1,''id'',''age'' 
UNION ALL
SELECT 2,''id'',''sex''
) A GROUP BY ID'
)

DROP TABLE T

(所影响的行数为 2 行)

id 01 02
------------------------------ ------------------------------ ------------------------------ 
name xiao li
age 21 22
sex 男 女

警告: 聚合或其它 SET 操作消除了空值。