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 <X86> (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 行受影响)

*/