My "cross tabulation" query Stored Procedure

Source: Internet
Author: User

Create procedure patongjixx
@ Datetimes datetime,
@ Datetimee datetime
As
Declare @ tbtiqu table (
Aid int identity (1, 1 ),
Anbie varchar (40 ),
Xiaqu varchar (24 ),
Isxingshi bit)

Declare @ tbxiaqu table (
Xid int identity (1, 1 ),
Xxiaqu varchar (24 ))

Declare @ tbresult table (
Rid int identity (1, 1 ),
Rxiaqu varchar (24 ),
Xingheji int,
Sharen int,
Qiangjie int,
Daoqie int,
Daoqiej int,
Qiangjian int,
Baozha int,
Toudu int,
Fandu int,
Xingqita int,
Zhian int,
Zaihai int,
Huojing int,
Shigu int,
Jqita int,
Qiuzhu int,
Zixuan int,
Saorao int,
Wqita int,
Zong INT)

Declare @ A1 int
Declare @ A2 int
Declare @ A3 int
Declare @ A4 int
Declare @ A5 int
Declare @ A6 int
Declare @ A7 int
Declare @ A8 int
Declare @ A9 int
Declare @ A10 int
Declare @ A11 int
Declare @ A12 int
Declare @ A13 int
Declare @ A14 int
Declare @ A15 int
Declare @ A16 int
Declare @ A17 int
Declare @ A18 int
Declare @ A19 int
Declare @ A20 int

Declare @ B1 int
Declare @ B2 int
Declare @ B3 int
Declare @ B4 int
Declare @ B5 int
Declare @ B6 int
Declare @ B7 int
Declare @ B8 int
Declare @ B9 int
Declare @ B10 int
Declare @ B11 int
Declare @ B12 int
Declare @ B13 int
Declare @ B14 int
Declare @ B15 int
Declare @ F8 int
Declare @ B17 int
Declare @ B18 int
Declare @ B19 int
Declare @ B20 int

Set @ b1 = 0
Set @ b2 = 0
Set @ B3 = 0
Set @ B4 = 0
Set @ B5 = 0
Set @ B6 = 0
Set @ B7 = 0
Set @ B8 = 0
Set @ B9 = 0
Set @ B10 = 0
Set @ B11 = 0
Set @ B12 = 0
Set @ B13 = 0
Set @ B14 = 0
Set @ B15 = 0
Set @ F8 = 0
Set @ B17 = 0
Set @ B18 = 0
Set @ B19 = 0
Set @ B20 = 0

Declare @ xiaqu varchar (24)
Declare @ current int
Declare @ allcount int
Set @ current = 1

Insert @ tbtiqu (anbie, xiaqu, isxingshi) Select anbie, xiaqu, isxingshi from tanjian where jiejingtime> @ datetimes and jiejingtime <@ datetimee

Insert @ tbxiaqu (xxiaqu) values ('pudong Institute ')
Insert @ tbxiaqu (xxiaqu) values ('puxi ')
Insert @ tbxiaqu (xxiaqu) values ('zhuang suo ')
Insert @ tbxiaqu (xxiaqu) Values ')
Insert @ tbxiaqu (xxiaqu) values ('manager authorization ')
Insert @ tbxiaqu (xxiaqu) values ('luogang ')
Insert @ tbxiaqu (xxiaqu) values ('meng gang ')
Insert @ tbxiaqu (xxiaqu) Values ')
Insert @ tbxiaqu (xxiaqu) values ('wu qiusuo ')
Insert @ tbxiaqu (xxiaqu) values ('zhao disuo ')
Insert @ tbxiaqu (xxiaqu) values ('your home ')
Insert @ tbxiaqu (xxiaqu) values ('ding zhisuo ')
Insert @ tbxiaqu (xxiaqu) values ('square Miles ')
Insert @ tbxiaqu (xxiaqu) values ('full cune ')
Insert @ tbxiaqu (xxiaqu) Values ')
Insert @ tbxiaqu (xxiaqu) values ('all phases ')
Insert @ tbxiaqu (xxiaqu) values ('changcun ')
Insert @ tbxiaqu (xxiaqu) values ('zhangzhansuo ')
Set @ allcount = (select top 1 Xid from @ tbxiaqu order by Xid DESC)

While @ current <= @ allcount
Begin
Set @ xiaqu = (select xxiaqu from @ tbxiaqu where Xid = @ current)
 
Select @ a1 = count (*) from @ tbtiqu where isxingshi = 1 and xiaqu = @ xiaqu
Select @ a2 = count (*) from @ tbtiqu where anbie in ('murder ', 'Damage lethal') and xiaqu = @ xiaqu
Select @ a3 = count (*) from @ tbtiqu where anbie in ('robbery, 'snatch') and xiaqu = @ xiaqu
Select @ A4 = count (*) from @ tbtiqu where anbie = 'theft 'and xiaqu = @ xiaqu
Select @ A5 = count (*) from @ tbtiqu where anbie = 'Vehicle theft 'and xiaqu = @ xiaqu
Select @ A6 = count (*) from @ tbtiqu where anbie = 'rape 'and xiaqu = @ xiaqu
Select @ A7 = count (*) from @ tbtiqu where anbie = 'explosive 'and xiaqu = @ xiaqu
Select @ A8 = count (*) from @ tbtiqu where anbie = 'poisoning 'and xiaqu = @ xiaqu
Select @ A9 = count (*) from @ tbtiqu where anbie = 'traffic' and xiaqu = @ xiaqu
Select @ A10 = @ A1 + @ A2 + @ A3 + @ A4 + @ A5 + @ A6 + @ A7 + @ A8 + @ A9

Select @ A11 = count (*) from @ tbtiqu where anbie = 'public Security case' and xiaqu = @ xiaqu
Select @ A12 = count (*) from @ tbtiqu where anbie = 'public security disaster (Accident) 'and xiaqu = @ xiaqu
Select @ A13 = count (*) from @ tbtiqu where anbie = 'fire' and xiaqu = @ xiaqu
Select @ A14 = count (*) from @ tbtiqu where anbie = 'traffic accident 'and xiaqu = @ xiaqu
Select @ A15 = count (*) from @ tbtiqu where anbie = 'other alert class' and xiaqu = @ xiaqu
Select @ A16 = count (*) from @ tbtiqu where anbie = 'Accept the requests' and xiaqu = @ xiaqu
Select @ A17 = count (*) from @ tbtiqu where anbie = 'consulting 'and xiaqu = @ xiaqu
Select @ A18 = count (*) from @ tbtiqu where anbie = 'harassing 'and xiaqu = @ xiaqu
Select @ A19 = count (*) from @ tbtiqu where anbie = 'other invalid alerts 'and xiaqu = @ xiaqu
Select @ A20 = @ A10 + @ A11 + @ A12 + @ A13 + @ A14 + @ A15 + @ A16 + @ A17 + @ A18 + @ A19

Insert @ tbresult (Region, xingheji, sharen, qiangjie, region, region, Qiangjian, baozha, toudu, fandu, xingqita, Zhian, zaihai, Shanghai, Shigu, jqita, Qingdao, Qingdao, saorao, wqita, Zong)
Values (@ xiaqu, @ A1, @ A2, @ A3, @ A4, @ A5, @ A6, @ A7, @ A8, @ A9, @ A10, @ A11, @ A12, @ A13, @ A14, @ A15, @ A16, @ A17, @ A18, @ A19, @ A20)

Set @ b1 = @ B1 + @ A1
Set @ b2 = @ B2 + @ A2
Set @ B3 = @ B3 + @ A3
Set @ B4 = @ B4 + @ A4
Set @ B5 = @ B5 + @ A5
Set @ B6 = @ B6 + @ A6
Set @ B7 = @ B7 + @ A7
Set @ B8 = @ B8 + @ A8
Set @ B9 = @ B9 + @ A9
Set @ B10 = @ B10 + @ A10
Set @ B11 = @ B11 + @ A11
Set @ B12 = @ B12 + @ A12
Set @ B13 = @ B13 + @ A13
Set @ B14 = @ B14 + @ A14
Set @ B15 = @ B15 + @ A15
Set @ F8 = @ F8 + @ A16
Set @ B17 = @ B17 + @ A17
Set @ B18 = @ B18 + @ A18
Set @ B19 = @ B19 + @ A19
Set @ B20 = @ B20 + @ A20

Set @ current = @ current + 1
End
Insert @ tbresult (Region, xingheji, sharen, qiangjie, region, region, Qiangjian, baozha, toudu, fandu, xingqita, Zhian, zaihai, Shanghai, Shigu, jqita, Qingdao, Qingdao, saorao, wqita, Zong)
Values ('total: ', @ B1, @ B2, @ B3, @ B4, @ B5, @ B6, @ B7, @ B8, @ B9, @ B10, @ B11, @ B12, @ B13, @ B14, @ B15, @ B6, @ B17, @ B18, @ B19, @ B20)
Select * From @ tbresult
Go

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.