Create table tt(pid int,pcid varchar(50),pcname varchar(200),managername varchar(200))insert into tt values(1 , '1' , '销售','张三')
insert into tt values(1 , '2' , '经理','李四')select * from ttdrop table tt
------------我想要这个结果 不知道怎么实现。pid 销售 经理
1 张三 李四
调试欢乐多
from tt t
where not exists(select 1
from tt
where pid=t.pid and pcid>t.pcid)
insert into tt values(1 , '2' , '经理','李四')SELECT *
FROM (
SELECT pid, pcname, managername
FROM tt
) a
PIVOT(MAX(managername) FOR pcname IN (销售, 经理))b
/*
pid 销售 经理
1 张三 李四
*/
select
pid,
max(case pcname when '销售' then managername else '' end) as 销售,
max(case pcname when '经理' then managername else '' end) as 经理
from tt
group by pid/*
pid 销售 经理
----------- -------------------- --------------------
1 张三 李四(1 行受影响)
*/
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'max(case when pcname='''+pcname+''' then managername else '''' end) as ['+pcname+']'
from
(select distinct pcname from tt) texec ('select pid,'+@sql+' from tt group by pid')
/**
pid 经理 销售
----------- -------------------------------------
1 李四 张三(1 行受影响)
**/
insert into tt values(1 , '2' , '经理','李四')
DECLARE @S NVARCHAR(4000)SELECT @S=ISNULL(@S,'')+',MAX(CASE WHEN pcid='''+PCID+''' THEN managername ELSE '''' END ) AS '''+pcname+'''' from ttEXEC('SELECT PID'+ @S+' FROM TT GROUP BY PID')
/*PID 销售 经理
----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 张三 李四
set @cmd='select distinct pid,'
select @cmd=@cmd+''+pcname+'=case '''+pcname+''' when '''+pcname+''' then '''+managername+''' else null end, '
from (select pcname,managername from tt) t
set @cmd=substring(@cmd,0,len(@cmd)-charindex(',',reverse(@cmd))+2)
set @cmd=@cmd+' from tt'
exec(@cmd)
,max(case when pcname='销售' then managername else '' end) as 销售 ,
max(case when pcname='经理' then managername else '' end) as 经理 from tt group by pid
2 2005001 数学 90.0
3 2005001 英语 80.0
4 2005002 语文 56.0
5 2005002 数学 69.0
6 2005002 英语 89.0执行select sid,语文=isnull(sum(case course when '语文' then result end),0),
数学=isnull(sum(case course when '数学' then result end),0),
英语=isnull(sum(case course when '英语' then result end),0)
from result
group by sid
order by sid得出结果sid 语文 数学 英语 2005001 80.0 90.0 80.0
2005002 56.0 69.0 89.0 请关注有效提高asp.net 60%效率的 RKWeb1.1开发模板! www.hositech.com2)较为复杂的行转列表1:courseid name1 语文
2 数学
3 英语
表2:resultid sid course result1 2005001 语文 80.0
2 2005001 数学 90.0
3 2005001 英语 80.0
4 2005002 语文 56.0
5 2005002 数学 69.0
6 2005002 英语 89.0declare @sql varchar(8000)
set @sql='select sid'
select @sql=@sql+','+course.name+'=isnull(sum(case course when '''+course.name+''' then result end),0)'
from course order by id
set @sql=@sql+' from result group by sid order by sid'
print @sql
exec(@sql)得出结果sid 语文 数学 英语 2005001 80.0 90.0 80.0
2005002 56.0 69.0 89.0
销售=max(case when pcname='销售' then managername end ),
经理=max(case when pcname='经理' then managername end )
from tt
group by pid