SQL数据库行列转换问题
数据库表T的结构为：
NAME KJ BZ RQ
A 0 不正确 2013/12/3
A 1 存在问题 2013/12/1
B 1 正确 2013/12/1
B 0 注意 2013/12/3
B 1 可能 2013/12/2
A 1 行 2013/12/2
。。。。。
需要转化的表H结构如下：
NAME KJ1 BZ1 KJ2 BZ2 KJ3 BZ3
A 1 存在问题 1 行 0 不正确
B 1 正确 1 可能 0 注意
T表A只允许一天填报一个，重复不能提交，KJ1，BZ1就是1日填写的数据，KJ2,BZ2是2日填写的数据，依次类推。。
感觉有点复杂，水平有限，请各位帮忙解决，谢谢。
[解决办法]
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'
go
declare @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 tt
set @sql = 'select NAME'+@sql + ' from t group by name'
--select @sql
exec(@sql)
/*
NAMEKJ1BZ1 KJ2BZ2KJ3BZ3
A 1存在问题1行0不正确
B 1正确 1可能0注意
*/
create table #table(NAME varchar(20),KJ varchar(50),BZ varchar(50),RQ datetime)
insert into #table
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 行受影响)
