SQL结果集分列显示实例

----------------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date : 2010-07-06 21:13:04

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

-- Jul 9 2008 14:43:34

-- Copyright (c) 1988-2008 Microsoft Corporation

-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

-- Blog : http://blog.csdn.net/htl258

-- Subject: SQL 结果集分列显示实例

----------------------------------------------------------------------------------

--> 生成测试数据表: [tb]

IF OBJECT_ID([tb]) IS NOT NULL

DROP TABLE [tb]

GO

CREATE TABLE [tb] ([A] [nvarchar](10))

INSERT INTO [tb]

SELECT aa UNION ALL

SELECT bb UNION ALL

SELECT cc UNION ALL

SELECT dd UNION ALL

SELECT ee UNION ALL

SELECT ff UNION ALL

SELECT gg UNION ALL

SELECT hh UNION ALL

SELECT ii UNION ALL

SELECT jj UNION ALL

SELECT kk UNION ALL

SELECT ll

-->SQL查询如下:

--SQL2005

DECLARE @i INT

SET @i=4 --结果集显示的列数

DECLARE @cols VARCHAR(1000)

SELECT @cols=ISNULL(@cols+,,)+QUOTENAME(number+1)

FROM master..spt_values

WHERE TYPE=p AND number<@i

EXEC(

WITH t AS

(

SELECT rn=(ROW_NUMBER()OVER(ORDER BY GETDATE())++@i+-1)%+@i++1,*,

rn2=(ROW_NUMBER()OVER(ORDER BY GETDATE())++@i+-1)/+@i+

FROM tb

)

SELECT +@cols+

FROM t

PIVOT (MAX(a) FOR rn IN(+@cols+))b

)

/*

1 2 3 4

---------- ---------- ---------- ----------

aa bb cc dd

ee ff gg hh

ii jj kk ll

(3 行受影响)

*/