用怎样的SQL语句实现如下查询？

[rghjf]

有表1
id column1
1 102
1 103
1 104
2 105
2 106
2 107
要达到如下效果
id new column
1 102 103 104
2 105 106 107

[OpenVMS]

select a.*,(select count(*) from table1 where id=a.id and column1<=a.column1) IDX into #t1 from table1 a
select distinct a.id,
( select (case when idx=1 then column1 else '' end) from #t1 where id=a.id) '1',
( select (case when idx=2 then column1 else '' end) from #t1 where id=a.id) '2',
( select (case when idx=3 then column1 else '' end) from #t1 where id=a.id) '3'
from #t1 a

[duckcn]

如果你的new column是一个字段的话，可以参考下面帖子
http://www.csdn.net/expert/topic/632/632928.xml?temp=.2164575

[CSDNM]

CREATE TABLE #T(ID INT,[new column] VARCHAR(300))

SELECT * INTO #T1 FROM 表1
INSERT #T SELECT ID,MIN(column1) FROM #T1 GROUP BY ID
DELETE #T1 FROM #T1 A
WHERE column1=(SELECT MIN(column1) FROM #T1 WHERE ID=A.ID)

WHILE EXISTS (SELECT * FROM #T1)
BEGIN
UPDATE #T
SET [new column]=[new column]+' ' +B.column1
FROM #T A,#T1 B
WHERE A.ID=B.ID
AND B.column1=(SELECT MIN(column1) FROM #T1 WHERE ID=B.ID)
DELETE #T1 FROM #T1 A
WHERE column1=(SELECT MIN(column1) FROM #T1 WHERE ID=A.ID)
END

SELECT * FROM #T
DROP TABLE #T,#T1

[duckcn]

如果你是要求合并为new column字段，可以参考
http://www.csdn.net/expert/topic/632/632928.xml?temp=.1198999

[OpenVMS]

select a.*,(select count(*) from hp1 where id=a.id and column1<=a.column1) IDX into #t1 from hp1 a
select a.id,
sum(case when idx=1 then column1 else '' end) '1',
sum(case when idx=2 then column1 else '' end) '2',
sum(case when idx=3 then column1 else '' end) '3'
from #t1 a
group by id

[OpenVMS]

select a.*,(select count(*) from hp1 where id=a.id and column1<=a.column1) IDX into #t1 from hp1 a
select a.id,
sum(case when idx=1 then column1 else '' end) '1',
sum(case when idx=2 then column1 else '' end) '2',
sum(case when idx=3 then column1 else '' end) '3'
from #t1 a
group by id

[qybao]

如果不想用游标，可以这样试试
SELECT A.id,
newcolumn=B.column+' '+C.column+' '+D.column
FROM (SELECT DISTINCT id FROM yourtable) AS A,
yourtable AS B,
yourtable AS C,
yourtable AS D
WHERE A.id=B.id
and A.id=C.id
and A.id=D.id
and C.column<D.column
and C.column>B.column

[qybao]

如果不想用游标，可以这样试试
SELECT A.id,
newcolumn=B.column+' '+C.column+' '+D.column
FROM (SELECT DISTINCT id FROM yourtable) AS A,
yourtable AS B,
yourtable AS C,
yourtable AS D
WHERE A.id=B.id
and A.id=C.id
and A.id=D.id
and C.column<D.column
and C.column>B.column

[OpenVMS]

select a.*,(select count(*) from hp1 where id=a.id and column1<=a.column1) IDX into #t1 from hp1 a
select a.id,
sum(case when idx=1 then column1 else '' end) '1',
sum(case when idx=2 then column1 else '' end) '2',
sum(case when idx=3 then column1 else '' end) '3'
from #t1 a
group by id

[OpenVMS]

合并为一个字段吗?
select a.*,(select count(*) from hp1 where id=a.id and column1<=a.column1) IDX into #t1 from hp1 a
select a.id,
(rtrim(convert(varchar(10),sum(case when idx=1 then column1 else '' end)))+','+
rtrim(convert(varchar(10),sum(case when idx=2 then column1 else '' end))) +','+
rtrim(convert(varchar(10),sum(case when idx=3 then column1 else '' end)))) 'new column'
from #t1 a
group by id

[rghjf]

谢谢各位
本人查到一不错的方法，地址如下：
http://www.csdn.net/expert/topic/632/632928.xml?temp=.1198999

[CSDNM]

那是精华贴，当然不错，不过duckcn(xty) 早指出了！