pivot 透视

以下测试使用到的sql语句
[url]http://dl.iteye.com/topics/download/21402853-8309-34bc-897f-ed201e75ad4b[/url]

一、首先创建两个表：省份表、城市表
城市表中包含所属省份的ID
省份表中包含所属大区的ID

/****** Object:  Table [dbo].[City]    Script Date: 06/25/2011 07:02:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[City](
	[CityID] [int] IDENTITY(1,1) NOT NULL,
	[ProvinceID] [int] NOT NULL,
	[CityName] [nvarchar](50) NULL,
 CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED 
(
	[CityID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[City] ON
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (1, 1, N'北京')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (2, 2, N'天津')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (3, 3, N'石家庄')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (4, 3, N'唐山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (5, 3, N'秦皇岛')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (6, 3, N'邯郸')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (7, 3, N'邢台')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (8, 3, N'保定')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (9, 3, N'张家口')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (10, 3, N'承德')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (11, 3, N'沧州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (12, 3, N'廊坊')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (13, 3, N'衡水')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (14, 4, N'太原')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (15, 4, N'大同')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (16, 4, N'阳泉')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (17, 4, N'长治')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (18, 4, N'晋城')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (19, 4, N'朔州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (20, 4, N'晋中')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (21, 4, N'临汾')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (22, 4, N'忻州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (23, 4, N'运城')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (24, 5, N'呼和浩特')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (25, 5, N'包头')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (26, 5, N'乌海')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (27, 5, N'赤峰')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (28, 5, N'通辽')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (29, 6, N'沈阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (30, 6, N'大连')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (31, 6, N'鞍山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (32, 6, N'抚顺')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (33, 6, N'本溪')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (34, 6, N'丹东')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (35, 6, N'锦州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (36, 6, N'营口')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (37, 6, N'阜新')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (38, 6, N'辽阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (39, 6, N'盘锦')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (40, 6, N'铁岭')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (41, 6, N'朝阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (42, 6, N'葫芦岛')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (43, 7, N'长春')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (44, 7, N'吉林')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (45, 7, N'四平')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (46, 7, N'辽源')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (47, 7, N'通化')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (48, 7, N'白山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (49, 7, N'白城')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (50, 7, N'松原')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (51, 8, N'哈尔滨')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (52, 8, N'齐齐哈尔')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (53, 8, N'鸡西')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (54, 8, N'鹤岗')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (55, 8, N'双鸭山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (56, 8, N'大庆')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (57, 8, N'伊春')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (58, 8, N'佳木斯')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (59, 8, N'七台河')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (60, 8, N'牡丹江')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (61, 8, N'黑河')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (62, 8, N'绥化')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (63, 9, N'上海')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (64, 10, N'南京')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (65, 10, N'无锡')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (66, 10, N'徐州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (67, 10, N'常州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (68, 10, N'苏州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (69, 10, N'南通')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (70, 10, N'连云港')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (71, 10, N'淮阴')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (72, 10, N'盐城')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (73, 10, N'扬州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (74, 10, N'镇江')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (75, 10, N'泰州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (76, 10, N'宿迁')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (77, 11, N'杭州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (78, 11, N'宁波')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (79, 11, N'温州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (80, 11, N'嘉兴')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (81, 11, N'湖州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (82, 11, N'绍兴')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (83, 11, N'金华')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (84, 11, N'衢州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (85, 11, N'舟山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (86, 11, N'台州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (87, 11, N'丽水')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (88, 12, N'合肥')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (89, 12, N'芜湖')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (90, 12, N'蚌埠')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (91, 12, N'淮南')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (92, 12, N'马鞍山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (93, 12, N'淮北')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (94, 12, N'铜陵')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (95, 12, N'安庆')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (96, 12, N'黄山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (97, 12, N'阜阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (98, 12, N'毫州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (99, 12, N'宿州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (100, 12, N'滁州')
GO
print 'Processed 100 total records'
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (101, 12, N'巢湖')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (102, 12, N'六安')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (103, 12, N'池州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (104, 12, N'宣城')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (105, 13, N'福州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (106, 13, N'厦门')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (107, 13, N'三明')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (108, 13, N'莆田')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (109, 13, N'泉州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (110, 13, N'漳州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (111, 13, N'南平')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (112, 13, N'龙岩')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (113, 13, N'宁德')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (114, 14, N'南昌')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (115, 14, N'景德镇')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (116, 14, N'萍乡')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (117, 14, N'九江')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (118, 14, N'新余')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (119, 14, N'鹰潭')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (120, 14, N'上饶')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (121, 14, N'赣州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (122, 14, N'吉安')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (123, 14, N'宜春')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (124, 15, N'济南')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (125, 15, N'青岛')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (126, 15, N'淄博')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (127, 15, N'枣庄')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (128, 15, N'东营')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (129, 15, N'潍方')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (130, 15, N'烟台')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (131, 15, N'威海')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (132, 15, N'济宁')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (133, 15, N'泰安')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (134, 15, N'日照')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (135, 15, N'莱芜')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (136, 15, N'德州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (137, 15, N'滨州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (138, 15, N'临沂')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (139, 15, N'荷泽')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (140, 15, N'聊城')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (141, 16, N'郑州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (142, 16, N'开封')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (143, 16, N'洛阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (144, 16, N'平顶山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (145, 16, N'焦作')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (146, 16, N'鹤壁')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (147, 16, N'新乡')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (148, 16, N'安阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (149, 16, N'濮阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (150, 16, N'许昌')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (151, 16, N'漯河')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (152, 16, N'三门峡')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (153, 16, N'商丘')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (154, 16, N'周口')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (155, 16, N'驻马店')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (156, 16, N'信阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (157, 16, N'南阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (158, 17, N'武汉')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (159, 17, N'黄石')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (160, 17, N'襄樊')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (161, 17, N'十堰')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (162, 17, N'宜昌')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (163, 17, N'荆州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (164, 17, N'鄂州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (165, 17, N'孝感')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (166, 17, N'黄冈')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (167, 17, N'咸宁')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (168, 17, N'荆门')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (169, 17, N'随州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (170, 18, N'长沙')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (171, 18, N'株洲')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (172, 18, N'湘潭')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (173, 18, N'衡阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (174, 18, N'邵阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (175, 18, N'岳阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (176, 18, N'常德')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (177, 18, N'张家界')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (178, 18, N'娄底')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (179, 18, N'郴州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (180, 18, N'永州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (181, 18, N'怀化')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (182, 18, N'益阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (183, 19, N'广州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (184, 19, N'深圳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (185, 19, N'珠海')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (186, 19, N'汕头')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (187, 19, N'韶关')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (188, 19, N'河源')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (189, 19, N'梅州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (190, 19, N'惠州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (191, 19, N'汕尾')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (192, 19, N'东莞')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (193, 19, N'中山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (194, 19, N'江门')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (195, 19, N'佛山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (196, 19, N'阳江')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (197, 19, N'湛江')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (198, 19, N'茂名')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (199, 19, N'肇庆')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (200, 19, N'清远')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (201, 19, N'潮州')
GO
print 'Processed 200 total records'
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (202, 19, N'揭阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (203, 19, N'云浮')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (204, 20, N'南宁')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (205, 20, N'桂林')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (206, 20, N'梧州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (207, 20, N'北海')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (208, 20, N'玉林')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (209, 20, N'柳州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (210, 20, N'防城港')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (211, 20, N'钦州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (212, 20, N'贵港')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (213, 21, N'海口')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (214, 21, N'三亚')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (215, 22, N'重庆')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (216, 23, N'成都')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (217, 23, N'自贡')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (218, 23, N'广安')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (219, 23, N'攀枝花')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (220, 23, N'泸州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (221, 23, N'德阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (222, 23, N'绵阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (223, 23, N'广元')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (224, 23, N'遂宁')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (225, 23, N'内江')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (226, 23, N'乐山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (227, 23, N'宜宾')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (228, 23, N'南充')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (229, 23, N'资阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (230, 23, N'雅安')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (231, 23, N'巴中')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (232, 23, N'达州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (233, 24, N'贵阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (234, 24, N'六盘水')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (235, 24, N'遵义')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (236, 24, N'安顺')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (237, 25, N'昆明')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (238, 25, N'东川')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (239, 25, N'曲靖')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (240, 25, N'玉溪')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (241, 25, N'保山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (242, 26, N'拉萨')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (243, 26, N'日喀则')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (244, 27, N'西安')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (245, 27, N'铜川')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (246, 27, N'宝鸡')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (247, 27, N'咸阳')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (248, 27, N'延安')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (249, 27, N'渭南')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (250, 27, N'安康')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (251, 27, N'榆林')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (252, 27, N'汉中')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (253, 28, N'兰州')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (254, 28, N'金昌')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (255, 28, N'白银')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (256, 28, N'天水')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (257, 28, N'嘉峪关')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (258, 29, N'西宁')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (259, 30, N'银川')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (260, 30, N'石嘴山')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (261, 30, N'吴忠')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (262, 31, N'乌鲁木齐')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (263, 31, N'克拉玛依')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (264, 32, N'台北')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (265, 33, N'香港')
INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (266, 34, N'澳门')
SET IDENTITY_INSERT [dbo].[City] OFF

/****** Object:  Table [dbo].[Province]    Script Date: 06/25/2011 07:02:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Province](
	[ProvinceID] [int] IDENTITY(1,1) NOT NULL,
	[ProvinceName] [nvarchar](50) NULL,
	[RegionID] [int] NULL,
 CONSTRAINT [PK_Province] PRIMARY KEY CLUSTERED 
(
	[ProvinceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'该省属于哪个区' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Province', @level2type=N'COLUMN',@level2name=N'RegionID'
GO
SET IDENTITY_INSERT [dbo].[Province] ON
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (1, N'北京', 1)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (2, N'天津', 1)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (3, N'河北', 1)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (4, N'山西', 1)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (5, N'内蒙古', 1)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (6, N'辽宁', 2)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (7, N'吉林', 2)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (8, N'黑龙江', 2)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (9, N'上海', 3)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (10, N'江苏', 3)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (11, N'浙江', 3)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (12, N'安徽', 3)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (13, N'福建', 3)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (14, N'江西', 3)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (15, N'山东', 3)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (16, N'河南', 4)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (17, N'湖北', 4)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (18, N'湖南', 4)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (19, N'广东', 4)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (20, N'广西', 4)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (21, N'海南', 4)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (22, N'重庆', 5)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (23, N'四川', 5)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (24, N'贵州', 5)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (25, N'云南', 5)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (26, N'西藏', 5)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (27, N'陕西', 6)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (28, N'甘肃', 6)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (29, N'青海', 6)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (30, N'宁夏', 6)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (31, N'新疆', 6)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (32, N'台湾', 7)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (33, N'香港', 7)
INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (34, N'澳门', 7)
SET IDENTITY_INSERT [dbo].[Province] OFF

二、查询

select RegionID,p.ProvinceID,ProvinceName,COUNT(p.ProvinceID) c from City as c
join Province as p on c.ProvinceID = p.ProvinceID group by p.ProvinceID,ProvinceName,RegionID

查询结果
[img]http://dl.iteye.com/upload/attachment/504442/d3b72c14-b058-3008-b244-45865382e9bc.jpg[/img]

进一步想要这样的结果？
[img]http://dl.iteye.com/upload/attachment/504444/ed384b53-be72-30db-a8ea-aca226ec26ec.jpg[/img]

这样即可

select * from
(
select RegionID,p.ProvinceID,ProvinceName,COUNT(p.ProvinceID) c from City as c
join Province as p on c.ProvinceID = p.ProvinceID group by p.ProvinceID,ProvinceName,RegionID
) as t 
pivot (sum(t.c) for t.ProvinceName in
([北京],[天津],[河北],[山西],[内蒙古],[辽宁],[吉林],[黑龙江],[上海],[江苏],[浙江],[安徽],[福建],[江西],[山东]
,[河南],[湖北],[湖南],[广东],[广西],[海南],[重庆],[四川],[贵州],[云南],[西藏],[陕西],[甘肃],[青海],[宁夏],[新疆],[台湾],[香港],[澳门])
) as p

再进一步控制将列名转变为变量

declare @provinceStr nvarchar(1000)
set @provinceStr = '[北京],[天津],[河北],[山西],[内蒙古],[辽宁],[吉林],[黑龙江],[上海],[江苏],[浙江],[安徽],[福建],[江西],[山东]
,[河南],[湖北],[湖南],[广东],[广西],[海南],[重庆],[四川],[贵州],[云南],[西藏],[陕西],[甘肃],[青海],[宁夏],[新疆],[台湾],[香港],[澳门]'

declare @sql nvarchar(max)
set @sql = '
select * from
(
select RegionID,p.ProvinceID,ProvinceName,COUNT(p.ProvinceID) c from City as c
join Province as p on c.ProvinceID = p.ProvinceID group by p.ProvinceID,ProvinceName,RegionID
) as t 
pivot (sum(t.c) for t.ProvinceName in
({0})
) as p'
SET @sql= REPLACE(@sql,'{0}',@provinceStr)
exec sp_executesql @sql

再进一步将NULL值显示为0

declare @provinceStr nvarchar(1000)
set @provinceStr = '[北京],[天津],[河北],[山西],[内蒙古],[辽宁],[吉林],[黑龙江],[上海],[江苏],[浙江],[安徽],[福建],[江西],[山东]
,[河南],[湖北],[湖南],[广东],[广西],[海南],[重庆],[四川],[贵州],[云南],[西藏],[陕西],[甘肃],[青海],[宁夏],[新疆],[台湾],[香港],[澳门]';

declare @byname nvarchar(2000)
set @byname = 'coalesce(北京,0) as 北京,coalesce(天津,0) as 天津,coalesce(河北,0) as 河北,coalesce(山西,0) as 山西,coalesce(内蒙古,0) as 内蒙古,coalesce(辽宁,0) as 辽宁'

declare @sql nvarchar(max)
set @sql = '
select RegionID,ProvinceID,{1} from
(
select RegionID,p.ProvinceID,ProvinceName,COUNT(p.ProvinceID) c from City as c
join Province as p on c.ProvinceID = p.ProvinceID group by p.ProvinceID,ProvinceName,RegionID
) as t 
pivot (sum(t.c) for t.ProvinceName in
({0})
) as p'
SET @sql= REPLACE(@sql,'{0}',@provinceStr)
SET @sql= REPLACE(@sql,'{1}',@byname)
exec sp_executesql @sql

结果如下：

[img]http://dl.iteye.com/upload/attachment/504448/1e6293e5-7a5c-31ab-b5ea-6c7c67fbce95.jpg[/img]

以上结果的一个关键是使用pivot函数
语法：[url]http://technet.microsoft.com/zh-cn/library/ms177410.aspx[/url]

注意事项：
一、以下为预期查询数据

select RegionID,p.ProvinceID,ProvinceName,COUNT(p.ProvinceID) c from City as c  
join Province as p on c.ProvinceID = p.ProvinceID group by p.ProvinceID,ProvinceName,RegionID 

二、*号位置使用，可以用<非透视列>，<非透视列>代替，但<非透视列>并不是…位置查询存在的列名而是…位置列中存在的列值。
sum(t.c)为最后被用作列值的统计;
t.ProvinceName被用作列名;
in ()被用作列名的…位置列值

select * from  
(  
    …
) as t   
pivot (sum(t.c) for t.ProvinceName in ({0}) ) as p