例如: 编号
001
002
003
004
005
006
007
008
查询得出结果显示如下:
编号1 编号2 编号3
001 004 007
002 005 008
003 006 NULL
也就是隔3行换一次列
declare @tb table([col] varchar(3))
insert @tb
select '001' union all
select '002' union all
select '003' union all
select '004' union all
select '005' union all
select '006' union all
select '007' union all
select '008''
;WITH TEMP AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY getdate()) AS RID
FROM @tb
)
SELECT
col1=max(case when RID%3=1 then col end),
col2=max(case when RID%3=2 then col end),
col3=max(case when RID%3=0 then col end)
FROM TEMP
GROUP BY (RID-1)/3
得出的结果是:
编号1 编号2 编号3
001 002 003
004 005 006
007 008 NULL
搞不定,求助各位大神~~~
列转行