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, '女 '
select * from t
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
