怎么能让查询结果中的行列互换
例如 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 [email protected]+ ',['+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 [email protected]+'
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 操作消除了空值。