怎么能让查询结果中的行列互换例如 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怎么实现?
本来显示是 id name age sex
01 xiao 21 男
02 li 22 女
现在我想让 id 01 02
name xiao li
age 21 22
sex 男 女请问用SQL怎么实现?
(
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 COL1SET @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 操作消除了空值。
insert into person
select '01','xiao','21','男'
UNION ALL
select '02','li','22','女' create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'='''+case isnull(@new_fdname,'') when '' then ''
else @new_fdname+'=' end+''''''+name+''''''''
,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+cast(['+@fdname+'] as varchar)+'']=''''''+replace(['+name+'],'''','''''''')+'''''''' from ['+@tbname+']'
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdname
order by colidselect @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s4=substring(@s4,2,8000)
,@s5=substring(@s5,16,8000)exec('declare '+@s1+'
select '+@s2+@s3+'
select '+@s4+'
exec('+@s5+')')
goexec p_zj 'person','id','id' 结果:
id 01 02
name xiao li
age 21 22
sex 男 女