SQL 2005列转行的疑难问题
例如： 编号
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
[解决办法]
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
),
t1 as
(
select top 3 *,ROW_NUMBER() OVER(ORDER BY getdate()) AS rn from temp
),
t2 as
(
select top 3 *,ROW_NUMBER() OVER(ORDER BY getdate()) AS rn from temp where RID>(select MAX(RID) from t1)
),
t3 as
(
select top 3 *,ROW_NUMBER() OVER(ORDER BY getdate()) AS rn from temp where RID>(select MAX(RID) from t2)
)
select t1.col as 编号1,t2.col as 编号2,t3.col as 编号3
from t1
left join t2 on t1.rn=t2.rn
left join t3 on t1.rn=t3.rn
/*
编号1编号2编号3
001004007
002005008
003006NULL
*/
----------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2013-09-10 14:22:21
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
--Apr 2 2010 15:53:02
--Copyright (c) Microsoft Corporation
--Data Center Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] 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 F AS
(
SELECT *,ID=ROW_NUMBER()OVER(ORDER BY GETDATE()) FROM tb
)
SELECT
A.编号 AS 编号1,B.编号 AS 编号2,C.编号 AS 编号3
FROM
(SELECT * FROM F WHERE ((ID - 1) / 3) % 3 = 0) A
LEFT JOIN
(SELECT * FROM F WHERE ((ID - 1) / 3) % 3 = 1) B
ON
(A.ID-1)/3=(B.ID-1)/3-1
AND
(A.ID-1)%3=(B.ID-1)%3
LEFT JOIN
(SELECT * FROM F WHERE ((ID - 1) / 3) % 3 = 2) C
ON
(B.ID-1)/3=(C.ID-1)/3-1
AND
(B.ID-1)%3=(C.ID-1)%3
----------------结果----------------------------
/*编号1 编号2 编号3
---- ---- ----
001 004 007
002 005 008
003 006 NULL
(3 行受影响)
*/
