将行转换为列 [英] conversion row to column
本文介绍了将行转换为列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
quesid questext optionid得分表现
1 gdggf 1 10优秀
2 hghgkjhkjh 1 5优秀
3 ijk 2 12好>
1 gdggf 3 3平均
1 gdggf 4 4差
1 gdggf 1 10优秀
可以将此表转换为
quesid questext非常好平均穷人
1 gdggf 20 0 3 4
2 hghgkjhkjh 5 0 0 0
3 ijk 0 12 0 0
quesid questext optionid score performance
1 gdggf 1 10 Excellent
2 hghgkjhkjh 1 5 Excellent
3 ijk 2 12 Good
1 gdggf 3 3 Average
1 gdggf 4 4 Poor
1 gdggf 1 10 excellent
can i convert this table in to
quesid questext excelent good average poor
1 gdggf 20 0 3 4
2 hghgkjhkjh 5 0 0 0
3 ijk 0 12 0 0
推荐答案
嗨...
检查以下链接
行到SQL Server 2000中的列 [ ^ ]
将行转换为列的五种方法 [ ^ ]
将行转换为列 [ ^ ]
问候,
GVPrabu
Hi...
Check the following links
Rows to Columns in SQL Server 2000[^]
Five methods converting rows to columns[^]
Convert Rows to Columns[^]
Regards,
GVPrabu
检查:
Check this:
CREATE TABLE Aver(ques_id int,qtxt varchar(20),opid int,score int,performance varchar(30))
insert into Aver values( 1,'gdggf',1,10,'Excellent'),( 2,'6t7ik,ikik',2,12,'Good'),(1,'gdggf',3,3,'Average'),(1,'gdggf',4,4,'Poor')
select * from Aver
declare @tab1e table(Id int,Qtxt varchar(20),Excellent int,Good int,Average int,Poor int)
insert into @tab1e(ID)
select distinct ques_id from Aver
update T set T.Qtxt=A.qtxt,T.Excellent=A.score from @tab1e T inner Join Aver A on A.ques_id=T.Id where A.performance='Excellent'
update T set T.Qtxt=A.qtxt,T.Good=A.score from @tab1e T inner Join Aver A on A.ques_id=T.Id where A.performance='Good'
update T set T.Qtxt=A.qtxt,T.Average=A.score from @tab1e T inner Join Aver A on A.ques_id=T.Id where A.performance='Average'
update T set T.Qtxt=A.qtxt,T.Poor=A.score from @tab1e T inner Join Aver A on A.ques_id=T.Id where A.performance='Poor'
select Id,Qtxt,case when Excellent is null then 0 else Excellent end 'Excellent',case when Good is null then 0 else Good end 'Good',
case when Average is null then 0 else Average end 'Average',case when Poor is null then 0 else Poor end 'Poor'from @tab1e
这篇关于将行转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文