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())+'[email protected]+'-1)%'[email protected]+'+1,*,

           rn2=(ROW_NUMBER()OVER(ORDER BY GETDATE())+'[email protected]+'-1)/'[email protected]+'

       FROM tb

    )

    SELECT '[email protected]+'

    FROM t

       PIVOT (MAX(a) FOR rn IN('[email protected]+'))b

')

本文來自CSDN部落格，轉載請标明出處：http://blog.csdn.net/htl258/archive/2010/07/06/5717054.aspx