很急的问题，数据项的分项问题

[竹君子]

如

ID|Result
1 |aaa
1 |bbb
1 |fff
2 |ccc
2 |ddd
2 |eee
变成
Result1|Result2
aaa | ccc
bbb | ddd
fff | eee

[竹君子]

那我增加一个字段Num，根据Num排序，没有的置空，怎么实现那？感谢了阿，我再加分阿
ID|Result|Num
1 |aaa |1
1 |bbb |2
1 |fff |3
2 |ccc |2
2 |ddd |3
2 |eee |4
变成

Num| Result1|Result2
1 | aaa |
2 | bbb |ccc
3 | fff |ddd
4 | |eee

[zjcxc]

select Num=isnull(a.num,b.num)
,Result1=isnull(a.result,'')
,Result2=isnull(b.result,'')
from(
select result,num from 表 where id=1
) a full join (
select result,num from 表 where id=2
) b on a.num=b.num
order by num

[zjcxc]

--创建数据测试环境
declare @tb table(ID int,Result varchar(4),Num int)
insert into @tb
select 1,'aaa',1
union all select 1,'bbb',2
union all select 1,'fff',3
union all select 2,'ccc',2
union all select 2,'ddd',3
union all select 2,'eee',4

--查询结果
select Num=isnull(a.num,b.num)
,Result1=isnull(a.result,'')
,Result2=isnull(b.result,'')
from(
select result,num from @tb where id=1
) a full join (
select result,num from @tb where id=2
) b on a.num=b.num
order by num


/*--上面语句的执行结果
Num Result1 Result2
----------- ------- -------
1 aaa
2 bbb ccc
3 fff ddd
4 eee

（所影响的行数为 4 行）
--*/

[zhouzdsoft]

注释abc为表名

select a.*,b.result,c.result
from (select distinct num from abc) a left join (select * from abc where id=1) b on a.num=b.num left join (select * from abc where id=2) c on a.num=c.num

[竹君子]

谢谢
我理解一下
不懂再问

[竹君子]

谢谢 zjcxc(邹建)，大力各位。
如果我 有一列数据项，是由除法运算的来，但是可能除数为零
当除数为零时，自动置空，该怎么实现那

[pengdali]

SELECT *, 0 flag
INTO #
FROM QuestionResult
ORDER BY SurveyID DECLARE @a int, @b int

UPDATE #
SET @a = CASE WHEN @b = SurveyID THEN @a + 1 ELSE 1 END,
@b = SurveyID, flag = @a

SELECT a.AvgScore, b.AvgScore
FROM # a, # b
WHERE a.SurveyID = 1 AND b.SurveyID = 2 AND
a.flag = b.flag
go
DROP TABLE CompareScore

[pengdali]

如果我 有一列数据项，是由除法运算的来，但是可能除数为零
当除数为零时，自动置空，该怎么实现那


select case when 你的列<>0 then 被除数列/你的列 else 0 end from 表

[竹君子]

我是在视图中创建的，用的是邹建的方法，select case when 你的列<>0 then 被除数列/你的列 else 0 end from 表
该放那儿呢？

[竹君子]

可以了，但是列名怎么设呢

[竹君子]

可以了
谢谢阿
可惜我没多少分了
不然都给你们露

[pengdali]

select *,0 flag into # from 表 order by id

declare @a int,@b int

update # set @a=case when @b=id then @a+1 else 1 end,@b=id,flag=@a

select a.result,b.result from # a,# b where a.id=1 and b.id=2 and a.flag=b.flag
go
drop table #

[txlicenhe]

Select identity(int,1,1) as id,result as result1 into #tmp1 from table where id=1
Select identity(int,1,1) as id,result as result2 into #tmp2 from table where id=2
Select result1,result2 from #tmp1 join #tmp2 on #tmp1.id = #tmp2.id

[竹君子]

这个在数据库里写在哪里呢？
窃切的问

[pengdali]

开始菜单-->sqlserver程序组-->查询分析器

[竹君子]

请问＃的作用是社么

[竹君子]

to 大力：

go附近有错误？

SELECT *, 0 flag
INTO CompareScore
FROM QuestionResult
ORDER BY SurveyID DECLARE @a int, @b int
UPDATE #
SET @a = CASE WHEN @b = SurveyID THEN @a + 1 ELSE 1 END,
@b = SurveyID, flag = @a
SELECT a.AvgScore, b.AvgScore
FROM # a, # b
WHERE a.SurveyID = 1 AND b.SurveyID = 2 AND
a.flag = b.flag go DROP TABLE CompareScore