行列转换的问题,简单的一次行列转换用pviot函数直接就能实现,但是这个情况有些特殊
直接看sql语句吧--准备数据
create table #sonPara
(
studentNO varchar(10),
acct_dt varchar(10),
shuxue varchar(10),
yuwen varchar(10))insert into #sonPara values(1000000001,'2012/09/17','100','90')
insert into #sonPara values(1000000001,'2012/09/18','95','80')select * from #sonPara此时的数据是
studentNO acct_dt shuxue yuwen
1000000001 2012/09/17 100 90
1000000001 2012/09/18 95 80想要变成的数据形式是
studentNO 2012/09/17 2012/09/17 2012/09/18 2012/09/18
1000000001 100 90 95 80 求高手指教
直接看sql语句吧--准备数据
create table #sonPara
(
studentNO varchar(10),
acct_dt varchar(10),
shuxue varchar(10),
yuwen varchar(10))insert into #sonPara values(1000000001,'2012/09/17','100','90')
insert into #sonPara values(1000000001,'2012/09/18','95','80')select * from #sonPara此时的数据是
studentNO acct_dt shuxue yuwen
1000000001 2012/09/17 100 90
1000000001 2012/09/18 95 80想要变成的数据形式是
studentNO 2012/09/17 2012/09/17 2012/09/18 2012/09/18
1000000001 100 90 95 80 求高手指教
/*
create table #sonPara
(
studentNO varchar(10),
acct_dt varchar(10),
shuxue varchar(10),
yuwen varchar(10))
*/declare @sql varchar(max)
set @sql = 'select studentNo'
select @sql = @sql + ',sum(case acct_dt when '''+acct_dt+''' then shuxue else 0 end) as ['+acct_dt+'_shuxue]'
+ ',sum(case acct_dt when '''+acct_dt+''' then yuwen else 0 end) as ['+acct_dt+'_yuwen]'
from #sonPara
group by acct_dt
order by acct_dtselect @sql = @sql + ' from #sonPara group by studentNo'exec(@sql)--try it
写一个类似的结果create table #sonPara
(
studentNO varchar(10),
acct_dt varchar(10),
shuxue int ,
yuwen int)insert into #sonPara values(1000000001,'2012/09/17',100,90)
insert into #sonPara values(1000000001,'2012/09/18',95,80)select * from
(
select * from #sonPara PIVOT(MAX(shuxue) FOR acct_dt IN ([2012/09/17],[2012/09/18])) pit
) t
PIVOT (MAX(yuwen) FOR acct_dt IN ([2012/09/17],[2012/09/18])) pitSELECT * FROM
(
SELECT studentNO,rn=row_number()over(order by acct_dt,score desc), score
FROM #sonPara
UNPIVOT(score FOR col IN (shuxue,yuwen)) a
) b
PIVOT (MAX(score) FOR rn IN ([1],[2],[3],[4])) pit
/*
studentNO 1 2 3 4
1000000001 100 90 95 80*/
(
SELECT studentNO,rn=row_number()over(order by acct_dt,score desc), score
FROM #sonPara
UNPIVOT(score FOR col IN (shuxue,yuwen)) a
) b
PIVOT (MAX(score) FOR rn IN ([1],[2],[3],[4])) pit
(studentNO varchar(10),
acct_dt varchar(10),
shuxue varchar(10),
yuwen varchar(10))insert into #sonPara values(1000000001,'2012/09/17','100','90')
insert into #sonPara values(1000000001,'2012/09/18','95','80')select * from #sonPara/*
studentNO acct_dt shuxue yuwen
---------- ---------- ---------- ----------
1000000001 2012/09/17 100 90
1000000001 2012/09/18 95 80(2 row(s) affected)
*/
declare @sql varchar(6000)='select studentNO,'select @sql=@sql
+'sum(case when acct_dt='''+acct_dt+''' then shuxue else 0 end) as ['+acct_dt+'],'
+'sum(case when acct_dt='''+acct_dt+''' then yuwen else 0 end) as ['+acct_dt+'],'
from (select distinct acct_dt from #sonPara) t order by acct_dtselect @sql=left(@sql,len(@sql)-1)+' from #sonPara group by studentNO'exec(@sql)/*
studentNO 2012/09/17 2012/09/17 2012/09/18 2012/09/18
---------- ----------- ----------- ----------- -----------
1000000001 100 90 95 80(1 row(s) affected)
*/