--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([号码] VARCHAR(1),[月份] INT) INSERT [tb] SELECT 'B',200910 UNION ALL SELECT 'B',201001 UNION ALL SELECT 'B',201002 UNION ALL SELECT 'B',201003 --------------开始查询-------------------------- SELECT DISTINCT [号码],[月份]=(select ISNULL(''+' ','')+LTRIM([月份]) FROM tb WHERE [号码]=t.[号码] FOR XML PATH('')) FROM [tb]t ----------------结果---------------------------- /* 号码 月份 ---- ------------------------------- B 200910 201001 201002 201003 */
select * from tb piovt (max(字段) for 月份 in('200910','201001','201002','201003'))t
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2012-08-01 11:51:43 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([号码] varchar(1),[月份] int) insert [tb] select 'B',200910 union all select 'B',201001 union all select 'B',201002 union all select 'B',201003 --------------开始查询-------------------------- select * from tb a pivot (max(月份) for 月份 in([200910],[201001],[201002],[201003]))b ----------------结果---------------------------- /* 号码 200910 201001 201002 201003 ---- ----------- ----------- ----------- ----------- B 200910 201001 201002 201003(1 行受影响)*/
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([号码] VARCHAR(1),[月份] INT)
INSERT [tb]
SELECT 'B',200910 UNION ALL
SELECT 'B',201001 UNION ALL
SELECT 'B',201002 UNION ALL
SELECT 'B',201003
--------------开始查询--------------------------
SELECT DISTINCT [号码],[月份]=(select ISNULL(''+' ','')+LTRIM([月份]) FROM tb WHERE [号码]=t.[号码] FOR XML PATH('')) FROM [tb]t
----------------结果----------------------------
/*
号码 月份
---- -------------------------------
B 200910 201001 201002 201003
*/
*
from
tb
piovt
(max(字段) for 月份 in('200910','201001','201002','201003'))t
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-08-01 11:51:43
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([号码] varchar(1),[月份] int)
insert [tb]
select 'B',200910 union all
select 'B',201001 union all
select 'B',201002 union all
select 'B',201003
--------------开始查询--------------------------
select
*
from
tb a
pivot
(max(月份) for 月份 in([200910],[201001],[201002],[201003]))b
----------------结果----------------------------
/* 号码 200910 201001 201002 201003
---- ----------- ----------- ----------- -----------
B 200910 201001 201002 201003(1 行受影响)*/
感谢,学习了 FOR XML PATH
找了个详细的教程 http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html