将一列转换为多行

[lvoers]

如下：
Score
-----------
1
2
3
3
4
5
6
8

我想要的结果：
列名：1,2

结果：1,2
3,4
5,6
7,8

[usher_gml]

UP..没理解.

[子陌红尘]

楼主给的数据和输出结果匹配不上。

[--小F--]

[Quote=引用 4 楼 libin_ftsafe 的回复:]
楼主给的数据和输出结果匹配不上。
[/Quote]
大侠 我估计楼主掉了个7

[ai_li7758521]

DECLARE @tb TABLE(Score int)
insert @tb 
SELECT 1  UNION ALL
SELECT 2  UNION ALL
SELECT 3  UNION ALL
SELECT 4  UNION ALL
SELECT 5  UNION ALL
SELECT 6  UNION ALL
SELECT 7  UNION ALL
SELECT 8 

;with cte 
as
(
	select num=row_number() over(order by Score),Score
	from @tb
)

select  a.Score Score1,b.Score Score2
from cte a join cte b on a.num+1=b.num
where a.num%2=1 and b.num%2=0

Score1       Score2
----------- -----------
1           2
3           4
5           6
7           8

(4 行受影响)

[ai_li7758521]

DECLARE @tb TABLE(Score int)
insert @tb 
SELECT 1  UNION ALL
SELECT 2  UNION ALL
SELECT 3  UNION ALL
SELECT 3  UNION ALL
SELECT 4  UNION ALL
SELECT 5  UNION ALL
SELECT 6  UNION ALL
SELECT 8 

;with cte 
as
(
	select num=row_number() over(order by Score),Score
	from @tb
)

select  a.Score Score1,b.Score Score2
from cte a join cte b on a.num+1=b.num
where a.num%2=1 and b.num%2=0

Score1      Score2
----------- -----------
1           2
3           3
4           5
6           8

(4 行受影响)

按lz给的数据的结果

[Jamin_Liu]

沒理解

[lvoers]

不好意思。数据里少了一个7

[lvoers]

再问一个弱弱的问题。
我如果要转换五列一行呢?

[Jamin_Liu]

--測試資料
declare @table table
(
score int
);
insert into @table
values(1)
,(2)
,(3)
,(3)
,(4)
,(5)
,(6)
,(7)
,(8)
;
--查詢
declare @s int=5;

select STUFF((select ','+cast(Score as varchar) [text()]
from (select distinct Score from @table) t2
where (case when Score%@s>0 then score/@s+1 else score/@s end)=t1.col1
for xml path('')
)
,1,1,'')
from (
select distinct case when Score%@s>0 then score/@s+1 else score/@s end as col1
from @table
) t1
group by col1

[lvoers]

楼上的大哥。您这语法通不过阿

[feixianxxx]

DECLARE @tb TABLE(score int)
insert @tb 
SELECT 1  UNION ALL
SELECT 2  UNION ALL
SELECT 3  UNION ALL
SELECT 4  UNION ALL
SELECT 5  UNION ALL
SELECT 6  UNION ALL
SELECT 7  UNION ALL
SELECT 8 

select identity(int,1,1) as id,score AS '1' ,null as '2'   into #k1 from @tb where Score%2=0  and Score<>2
select identity(int,1,1) as id,null as '1',score as '2'  into #k2 from @tb where Score%2=1 and Score<>1
go
select max([2]) as '1' ,MAX([1]) as '2' from (select * from #k1 union  all select * from #k2 ) t group by id

/*

3	4
5	6
7	8
*/

[lvoers]

楼上的大哥。如果我想显示五列一行呢。

[Jamin_Liu]

[Quote=引用 11 楼 Jamin_Liu 的回复:]
--測試資料
declare @table table
(
score int
);
insert into @table
values(1)
,(2)
,(3)
,(3)
,(4)
,(5)
,(6)
,(7)
,(8)
;
--查詢
declare @s int=5;

select STUFF((select ','+cast(Score as varchar) [text()]
from (select distinct Score from @table) t2
where  (case when Score%@s>0 then score/@s+1 else score/@s end)=t1.col1
for xml path('')
)
,1,1,'')
from (
select distinct case…
[/Quote]
我試過了是可以執行的

[Jamin_Liu]

[Quote=引用 12 楼 lvoers 的回复:]
楼上的大哥。您这语法通不过阿
[/Quote]
我試過了是可以的，請問你的是2000，還是2000以上版本

[lvoers]

我的是SQL2000

[Jamin_Liu]

[Quote=引用 17 楼 lvoers 的回复:]
我的是SQL2000

[/Quote]
--SQL SERVER2000試試這個
--測試資料
declare @table table
(
score int
);
insert into @table
select 1
union all select 2
union all select 3
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
;
--查詢
declare @s int=5;

select STUFF((select ','+cast(Score as varchar) [text()]
from (select distinct Score from @table) t2
where (case when Score%@s>0 then score/@s+1 else score/@s end)=t1.col1
for xml path('')
)
,1,1,'')
from (
select distinct case…

[lvoers]

这是错误信息：

服务器: 消息 170，级别 15，状态 1，行 7
第 7 行: ',' 附近有语法错误。
服务器: 消息 139，级别 15，状态 1，行 17
不能向局部变量赋予默认值。
服务器: 消息 137，级别 15，状态 1，行 21
必须声明变量 '@s'。
服务器: 消息 137，级别 15，状态 1，行 26
必须声明变量 '@s'。

然后我修改了

declare @s int=0 改成
declare @s int
set @s = 0


错误信息如下：
服务器: 消息 170，级别 15，状态 1，行 24
第 24 行: 'xml' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 30
第 30 行: 't1' 附近有语法错误。

[feixianxxx]

[Quote=引用 17 楼 lvoers 的回复:]
我的是SQL2000
[/Quote]
LZ怎么不试下13楼的

[Jamin_Liu]

我這邊沒有SQLSERVER2000的測試環境，所以調試不了

[ChinaJiaBing]

declare @score table (score int)
insert into @score select 1
      union all    select 2
      union all    select 3
      union all    select 4
      union all    select 5
      union all    select 6
      union all    select 7
      union all    select 8
select cast(a.score as nvarchar(10))+','+cast(b.score as nvarchar(10))  from @score a join @score b  on a.score=b.score-1
     where a.score%2!=0


(8 行受影响)

---------------------
1,2
3,4
5,6
7,8

(4 行受影响)