create table 表T
(NAME varchar(10),KJ int,BZ varchar(10),RQ varchar(15))insert into 表T
select 'A',0,'不正确','2013/12/3' union all
select 'A',1,'存在问题','2013/12/1' union all
select 'B',1,'正确','2013/12/1' union all
select 'B',0,'注意','2013/12/3' union all
select 'B',1,'可能','2013/12/2' union all
select 'A',1,'行','2013/12/2'
declare @tsql varchar(6000)select @tsql=isnull(@tsql+',','')
+'max(case when rn='+rtrim(number)+' then KJ else '''' end) ''KJ'+rtrim(number)+''', '
+'max(case when rn='+rtrim(number)+' then BZ else '''' end) ''BZ'+rtrim(number)+''' '
from master.dbo.spt_values
where type='P' and number between 1 and
(select max(c) from
(select count(1) 'c' from 表T group by NAME) t)select @tsql='select NAME,'+@tsql
+' from (select NAME,KJ,BZ,
row_number() over(partition by NAME order by RQ) ''rn''
from 表T) t
group by NAME'exec(@tsql)/*
NAME KJ1 BZ1 KJ2 BZ2 KJ3 BZ3
---------- ----------- ---------- ----------- ---------- ----------- ----------
A 1 存在问题 1 行 0 不正确
B 1 正确 1 可能 0 注意(2 row(s) affected)
*/
create table t(NAME varchar(10), KJ int, BZ varchar(20), RQ datetime)insert into t
select 'A', 0 ,'不正确', '2013/12/3'
union all select 'A', 1 ,'存在问题', '2013/12/1'
union all select 'B', 1 ,'正确', '2013/12/1'
union all select 'B', 0 ,'注意', '2013/12/3'
union all select 'B', 1 ,'可能', '2013/12/2'
union all select 'A', 1 ,'行', '2013/12/2'
godeclare @sql nvarchar(4000)set @sql = '';with tt
as
(
select convert(varchar(10),RQ,120) as rq,
ROW_NUMBER() over(order by rq) as rownum
from t
group by rq
)select @sql = @sql + ',max(case when rq = '''+ rq +''' then KJ else null end) as KJ' +
CAST(rownum as varchar) +
',max(case when rq = '''+ rq +''' then BZ else null end) as BZ' +
CAST(rownum as varchar)
from ttset @sql = 'select NAME'+@sql + ' from t group by name'--select @sqlexec(@sql)
/*
NAME KJ1 BZ1 KJ2 BZ2 KJ3 BZ3
A 1 存在问题 1 行 0 不正确
B 1 正确 1 可能 0 注意
*/
select 'A',0,'不正确','2013/12/3' union all
select 'A',1,'存在问题','2013/12/1' union all
select 'B',1,'正确','2013/12/1' union all
select 'B',0,'注意','2013/12/3' union all
select 'B',1,'可能','2013/12/2' union all
select 'A',1,'行','2013/12/2'
select * from #table--动态脚本,行转列。
declare @sql nvarchar(max);
declare @i int;
set @sql = ''
set @i = 1
select @sql = @sql +
',max(case when RQ=''' +
convert(varchar(10),RQ,120) +'''' +
' then KJ end) as KJ' + cast(@i as varchar)+
',max(case when RQ=''' +
convert(varchar(10),RQ,120) +'''' +
' then BZ end) as bz' + cast(@i as varchar),
@i = @i + 1
from #table
group by RQ
order BY RQ
set @sql = 'select NAME' + @sql + ' from #table
group by NAME'
exec(@sql)--------------------------------------------------------
NAME KJ1 bz1 KJ2 bz2 KJ3 bz3
-------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
A 1 存在问题 1 行 0 不正确
B 1 正确 1 可能 0 注意
警告: 聚合或其他 SET 操作消除了 Null 值。(2 行受影响)