`
njmnjx
  • 浏览: 234718 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

pivot 透视

SQLCC++C#ITeye 
阅读更多
以下测试使用到的sql语句
http://dl.iteye.com/topics/download/21402853-8309-34bc-897f-ed201e75ad4b

一、首先创建两个表：省份表、城市表
       城市表中包含所属省份的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

查询结果


进一步想要这样的结果？


这样即可 
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

结果如下：



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

注意事项：
一、以下为预期查询数据 
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

  • 大小: 45.1 KB
  • 大小: 201.7 KB
  • 大小: 58.2 KB
分享到：
| Asp.net与Flex交互测试
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    JIDE Pivot Grid(数据透视表格) 开发员技术手册

    ### JIDE Pivot Grid (数据透视表格) 开发员技术手册 #### 本文档目的 本技术手册旨在为希望在其应用程序中使用JIDE Pivot Grid（数据透视表格）的开发者提供指导。JIDE Pivot Grid是一款基于Java/Swing实现的数据...

    超级数据透视表：powerpivot.pdf

    PowerPivot 作为 Excel2013 商务智能标准插件，...读完本书您会了解， PowerPivot 已经在各方面完全超越了传统 Excel 数据透视表，它可以对数据源任意子集进行 各种比较和计算，让你的数据的分析变得更加灵活和方便。

    SQL Server 关于Pivot详解

    这个 Pivot 语句将成绩表转化为一个透视表，其中每一行代表一个学生，每一列代表一个课程号，单元格中的值为该学生在该课程的成绩。 实验题目 1. 使用 Pivot 语句将成绩表转化为透视表，显示每个学生的平均成绩。 ...

    搜集的Pivot例子

    Pivot，通常指的是数据透视表或数据透视图，是一种强大的数据分析工具，用于汇总、组织、分析和展示大量数据。在IT行业中，特别是数据分析和数据可视化领域，Pivot扮演着至关重要的角色。它允许用户通过拖放操作来...

    PowerPivot for Excel 帮助

    文档中解释了如何仅使用PowerPivot数据创建数据透视表和数据透视图，并且如何向数据透视表中添加切片器来筛选数据。 7. 准备数据分枂 数据分枂是数据分析的核心部分，文档提供了使用表和列的方法，创建新表、删除表...

    PowerPivot 入门手册_giantu54_PowerPivot_

    - PowerPivot中的数据透视表允许用户以交互方式探索和分析数据，通过拖放字段进行多维度分析。 - 切片器作为可视化工具，可帮助用户筛选和聚焦数据透视表的特定部分，增强分析的灵活性和深度。 5. **共享与协作**...

    用 Spire.Xls 生成的excel 透视表

    例如，你可以创建一个Excel工作簿，然后使用Spire.Xls的`Worksheet.AddPivotTable()`方法创建透视表，接着通过`PivotTable.Style`属性设置不同的内置样式，最后保存文件以查看样式效果。 以下是一个简单的代码示例...

    vue-pivot-table-数据透视表的vue组件-Vue.js开发

    vue-pivot-table数据透视表Live演示（jsfiddle）的vue组件安装npm install --save @ marketconnect / vue-pivot-table组件此项目vue-pivot-table数据透视表Live演示（jsfiddle）的vue组件npm install --save @ ...

    Oracle行转列之pivot

    总的来说，Oracle中的PIVOT和UNPIVOT关键字为数据透视和逆透视提供了强大的语法支持，使得数据转换查询更加直接和高效。这些技术的掌握对于数据库开发人员来说是不可或缺的，特别是在处理复杂报表和数据分析任务时。...

    创建PowerPivot工作簿

    数据透视表和数据透视图是PowerPivot中两种主要的数据可视化工具。数据透视表提供了一种灵活的方式，让用户根据需求重新组织和汇总数据，而数据透视图则以图表形式呈现数据，便于识别趋势和模式。 #### 切片器应用 ...

    PowerPivot的数据分析

    在某些情况下，作者甚至会探讨为什么不是所有的PivotTable（数据透视表）都应该用PowerPivot来构建，这有助于读者更好地理解PowerPivot的适用场景。 PowerPivot的核心功能之一是能够轻松导入和处理大量数据。书中将...

    Pivot-Generator:用纯（html5 js）生成数据透视表

    总的来说，"Pivot-Generator"是Web开发者实现数据可视化的有力工具，尤其适用于那些希望在网页上提供类似Excel透视表功能但又不想依赖大型JavaScript库的应用。通过深入研究其源码，开发者可以学习到如何使用...

    asp.net 实现Pivot表

    在IT行业中，Pivot表（也称为数据透视表）是一种强大的数据分析工具，它允许用户对大量数据进行快速的汇总和分析。在ASP.NET框架下，我们可以利用多种技术来实现Pivot表的功能，以便在Web应用中展示数据的聚合和转换...

    mjeppesen/matlab-pivot-table:数据透视表：使用表格数据对象的 matlab 数据透视表-matlab开发

    pivot_table：Matlab 的数据透视表介绍 此函数计算数据透视表（类似于在 Excel、R 或 Pandas (python) 中从 matlab 表中创建的数据透视表。换句话说，in 能够通过将大型数据集聚合到组中，然后应用函数来对其进行...

    微软excel2013用powerpivot建立数据模型-【配套内容示例文件】

    微软excel2013用powerpivot建立数据模型-【配套内容示例文件】 Microsoft Excel 2013 Building Data Models with PowerPivot] 注意！只有示例文件，没有电子书！ 内含一个access数据库文件；1-16章Excel的.xlsx配套...

    Python Pivot table透视表使用方法解析

    Pivot和Pivot_table函数都是对数据做透视表而使用的。其中的区别在于Pivot_table可以支持重复元素的聚合操作，而Pivot函数只能对不重复的元素进行聚合操作。 在一般的日常业务中，因为Pivot_table的功能更为强大,...

    Excel透视表报表生成组件ExcelPivot源码

    用类模块来封装Excel数据透视表生成的具体实现细节，提供一个用户界面收集报表的命名、存放位置等参数，设计一个自定义结构类型的变量来实现参数的传递，并将其封装成组件.因为项目引用了Office Excel COM组件，...

    pivot d3 的源代码

    "pivot d3" 是一个基于数据操作和可视化技术的库，它结合了Pivot Table（透视表）的功能和D3.js的数据驱动可视化库。Pivot Table是一种数据分析工具，用于汇总、组织和展示大量数据，而D3.js则是一个强大的...

Magicbox
Global site tag (gtag.js) - Google Analytics