请教SqlServer关于行专列问题
姓名 年龄 性别
--------------------------------------------
张一 23 女
张二 28 男
张三 27 男
张四 26 女
想变成(得到如下结果)：
字段名称 张一 张二 张三 张四
-----------------------------------------------------
年龄 23 28 27 26
性别 女 男 男 女
[最优解释]
--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
GO
create table [TB]([name] varchar(4),[age] int,[sex] varchar(2))
insert [TB]
select '张一',23,'女' union all
select '张二',28,'男' union all
select '张三',27,'男' union all
select '张四',26,'女'
DECLARE @sql VARCHAR(4000)
SET @sql='SELECT 字段名称=''年龄'''
SELECT @sql=@sql+','+QUOTENAME([NAME])+'=max(CASE WHEN [name]='''+[Name]+''' then rtrim(age) ELSE '''' end)'+CHAR(10) FROM TB t
SET @sql=@sql+' from [TB] union all select ''性别'''
SELECT @sql=@sql+','+QUOTENAME(RTRIM([NAME]))+'=max(CASE WHEN [name]='''+RTRIM([NAME])+''' then rtrim([sex]) ELSE '''' end)'+CHAR(10) FROM TB t
SET @sql=@sql+' from [TB]'
EXEC(@sql)
/*
字段名称 张一 张二 张三 张四
---- ------------ ------------ ------------ ------------
年龄 23 28 27 26
性别 女 男 男 女
(2 行受影响)
*/
drop table [TB]
if(object_id('a')is not null)drop table a
go
create table a
(
Name varchar(4),
age int,
sex varchar(2)
)
go
insert into a
select '张一',23,'女' union all
select '张二',28,'男' union all
select '张三',27,'男' union all
select '张四',26,'女'
--动态
declare @sql varchar(max)
declare @sql1 varchar(max)
select @sql = isnull(@sql+',',',') + ' max (case when [name]= '+quotename([name],'''')+' then cast('+quotename('age')+' as varchar) end ) as '+quotename([name]) from a group by name,age
select @sql1 = isnull(@sql1+',',',')+'max(case when [name]='+quotename([name],'''')+' then '+quotename('sex')+' end) as'+quotename([name]) from a group by name,age
print 'select ''年龄'' as ''字段名称'' '+@sql+'from a union all select ''性别'' as ''字段名称'' '+@sql1+' from a'
exec('select ''年龄'' as ''字段名称'' '+@sql+'from a union all select ''性别'' as ''字段名称'' '+@sql1+' from a' )
--静态
select '年龄' as '字段名称' , max (case when [name]= '张二' then cast([age] as varchar) end ) as [张二]
, max (case when [name]= '张三' then cast([age] as varchar) end ) as [张三]
, max (case when [name]= '张四' then cast([age] as varchar) end ) as [张四]
, max (case when [name]= '张一' then cast([age] as varchar) end ) as [张一]
from a
union all
select '性别' as '字段名称' ,max(case when [name]='张二' then [sex] end) as[张二]
,max(case when [name]='张三' then [sex] end) as[张三]
,max(case when [name]='张四' then [sex] end) as[张四]
,max(case when [name]='张一' then [sex] end) as[张一]
from a
/*字段名称 张二 张三 张四 张一
---- ------------------------------ ------------------------------ ------------------------------ ------------------------------
年龄 28 27 26 23
性别 男 男 女 女
警告: 聚合或其他 SET 操作消除了空值。
(2 行受影响)
*/
DECLARE @sql VARCHAR(4000)
Select @sql=isnull(@sql,'')+'union all SELECT '''+name+''' 字段名称'+(
SELECT ','+QUOTENAME([NAME])+'=max(CASE WHEN [name]='''+[Name]+''' then rtrim(age) ELSE '''' end)'+CHAR(10) FROM TB t
for xml path('')
)+' from tb ' from syscolumns Where id=object_id('tb')
set @sql=stuff(@sql,1,10,'')
exec(@sql)
