急急急急急急急，求高手指导 sql2008 pviot行列转换

[wangxianshou]

行列转换的问题，简单的一次行列转换用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

求高手指教。。。

[AcHerat]

建议楼主用动态行转列吧！这个转行的列具体值不确定，不可能只有这两条数据吧！

SQL动态行转列：

传送门

[AcHerat]

/*
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_dt

select @sql = @sql + ' from #sonPara group by studentNo'

exec(@sql)

--try it

[汤姆克鲁斯]

违背了基本原则，列名重复了

[汤姆克鲁斯]

我把分数的数据类型改成int 了
写一个类似的结果

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])) pit

SELECT * 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 * 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

[wangxianshou]

[Quote=引用 4 楼 的回复:]

我把分数的数据类型改成int 了
写一个类似的结果

SQL code
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……
[/Quote]

大哥，有没有动态的做法，这样只能静态的实现吧

[唐诗三百首]

try this,

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

(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_dt

select @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)
*/