--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([n] VARCHAR(3),[date] VARCHAR(7),[num] INT) INSERT [tb] SELECT 'joy','2011-01',10 UNION ALL SELECT 'use','2011-02',12 UNION ALL SELECT 'ing','2011-03',13 --------------开始查询-------------------------- DECLARE @sql VARCHAR(100) SELECT @sql =ISNULL(@sql+',','')+QUOTENAME([date]) FROM [tb] GROUP BY [date] --SELECT @sql EXEC('SELECT * FROM [tb] PIVOT (MAX(num) FOR[date] IN('+@sql+')) AS b')----------------结果---------------------------- /* n 2011-01 2011-02 2011-03 ---- ----------- ----------- ----------- ing NULL NULL 13 joy 10 NULL NULL use NULL 12 NULL(3 行受影响) */
--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([n] VARCHAR(3),[date] VARCHAR(7),[num] INT) INSERT [tb] SELECT 'joy','2011-01',10 UNION ALL SELECT 'use','2011-02',12 UNION ALL SELECT 'ing','2011-03',13 --------------开始查询--------------------------DECLARE @sql VARCHAR(8000) SET @sql = '' SELECT @sql = ISNULL(@sql + ',', '') + QUOTENAME([date]) + '=max(case when [date]=' + QUOTENAME([date], '''') + ' then [num] else 0 end)' FROM [tb] GROUP BY [date] EXEC('select [n]'+@sql+' from [tb] group by [n]')
----------------结果---------------------------- /* n 2011-01 2011-02 2011-03 ---- ----------- ----------- ----------- ing 0 0 13 joy 10 0 0 use 0 12 0(3 行受影响)*/
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([n] VARCHAR(3),[date] VARCHAR(7),[num] INT)
INSERT [tb]
SELECT 'joy','2011-01',10 UNION ALL
SELECT 'use','2011-02',12 UNION ALL
SELECT 'ing','2011-03',13
--------------开始查询--------------------------
DECLARE @sql VARCHAR(100)
SELECT @sql =ISNULL(@sql+',','')+QUOTENAME([date]) FROM [tb]
GROUP BY [date]
--SELECT @sql
EXEC('SELECT * FROM [tb]
PIVOT (MAX(num) FOR[date] IN('+@sql+')) AS b')----------------结果----------------------------
/*
n 2011-01 2011-02 2011-03
---- ----------- ----------- -----------
ing NULL NULL 13
joy 10 NULL NULL
use NULL 12 NULL(3 行受影响)
*/
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([n] VARCHAR(3),[date] VARCHAR(7),[num] INT)
INSERT [tb]
SELECT 'joy','2011-01',10 UNION ALL
SELECT 'use','2011-02',12 UNION ALL
SELECT 'ing','2011-03',13
--------------开始查询--------------------------DECLARE @sql VARCHAR(8000)
SET @sql = ''
SELECT @sql = ISNULL(@sql + ',', '') + QUOTENAME([date]) + '=max(case when [date]=' + QUOTENAME([date], '''') + ' then [num] else 0 end)'
FROM [tb]
GROUP BY [date]
EXEC('select [n]'+@sql+' from [tb] group by [n]')
----------------结果----------------------------
/*
n 2011-01 2011-02 2011-03
---- ----------- ----------- -----------
ing 0 0 13
joy 10 0 0
use 0 12 0(3 行受影响)*/